Can't SELECT from sysaudits 
Author Message
 Can't SELECT from sysaudits
I have about 200MB sybsecurity database with 11.0.3.3, and I activated
auditing of logins/logouts and errors.
The sybsecurity is almost empty (about 5% utilization).
Right now my sysaudits table is about 23K records, sysaudit_1 - 35K.

However, when I try to select from these tables, it hangs indefinitely.
I don't see any particular locks on the tables. Maybe I should create
indexes. I wonder if anyone done this on system tables.
Why would select be a problem, anyways.



Mon, 20 Jan 2003 03:00:00 GMT
 Can't SELECT from sysaudits

23,000 records is a lot of records to bring back to something like SQL
Advantage. Because you say that you are not being blocked, do a 'set
rowcount 1000' and see if you get anything returned. If you do, then you
know that it is the size of your result set that is the problem. If you
still get nothing back, then I would look again at the locking on your
server.

Andy


Quote:
> I have about 200MB sybsecurity database with 11.0.3.3, and I activated
> auditing of logins/logouts and errors.
> The sybsecurity is almost empty (about 5% utilization).
> Right now my sysaudits table is about 23K records, sysaudit_1 - 35K.

> However, when I try to select from these tables, it hangs indefinitely.
> I don't see any particular locks on the tables. Maybe I should create
> indexes. I wonder if anyone done this on system tables.
> Why would select be a problem, anyways.



Sat, 25 Jan 2003 03:00:00 GMT
 Can't SELECT from sysaudits
No I'm using plain old WISQL. I can't even select count(*) from sysaudits.
I know that every now and then I need to truncate the table after having
copied all records to sysaudits_X, although documentation never says that 20K
or 80K is a particular threshold to watch.

Anyhow, when I perform select count(*) from sysaudits there are no locks on
this table. Upon start, my SELECT process on 11.0.3.3, locks the table with
Sh_Intent & Sh_Page and never returns.
SELECT count(*) FROM sysaudits_1 returns me the number of rows right away.

Quote:

> 23,000 records is a lot of records to bring back to something like SQL
> Advantage. Because you say that you are not being blocked, do a 'set
> rowcount 1000' and see if you get anything returned. If you do, then you
> know that it is the size of your result set that is the problem. If you
> still get nothing back, then I would look again at the locking on your
> server.

> Andy



> > I have about 200MB sybsecurity database with 11.0.3.3, and I activated
> > auditing of logins/logouts and errors.
> > The sybsecurity is almost empty (about 5% utilization).
> > Right now my sysaudits table is about 23K records, sysaudit_1 - 35K.

> > However, when I try to select from these tables, it hangs indefinitely.
> > I don't see any particular locks on the tables. Maybe I should create
> > indexes. I wonder if anyone done this on system tables.
> > Why would select be a problem, anyways.



Sat, 25 Jan 2003 03:00:00 GMT
 Can't SELECT from sysaudits
Hi Tomas,

Have you tried running dbcc tablealloc/checktable against this table?
SELECTS that never return are sometimes caused by corrupt tables whose
page chain has formed a circular loop.

-bret

Quote:

> No I'm using plain old WISQL. I can't even select count(*) from sysaudits.
> I know that every now and then I need to truncate the table after having
> copied all records to sysaudits_X, although documentation never says that 20K
> or 80K is a particular threshold to watch.

> Anyhow, when I perform select count(*) from sysaudits there are no locks on
> this table. Upon start, my SELECT process on 11.0.3.3, locks the table with
> Sh_Intent & Sh_Page and never returns.
> SELECT count(*) FROM sysaudits_1 returns me the number of rows right away.


> > 23,000 records is a lot of records to bring back to something like SQL
> > Advantage. Because you say that you are not being blocked, do a 'set
> > rowcount 1000' and see if you get anything returned. If you do, then you
> > know that it is the size of your result set that is the problem. If you
> > still get nothing back, then I would look again at the locking on your
> > server.

> > Andy



> > > I have about 200MB sybsecurity database with 11.0.3.3, and I activated
> > > auditing of logins/logouts and errors.
> > > The sybsecurity is almost empty (about 5% utilization).
> > > Right now my sysaudits table is about 23K records, sysaudit_1 - 35K.

> > > However, when I try to select from these tables, it hangs indefinitely.
> > > I don't see any particular locks on the tables. Maybe I should create
> > > indexes. I wonder if anyone done this on system tables.
> > > Why would select be a problem, anyways.



Sat, 25 Jan 2003 03:00:00 GMT
 Can't SELECT from sysaudits
Bingo. You're good!
Quote:

> Hi Tomas,

> Have you tried running dbcc tablealloc/checktable against this table?
> SELECTS that never return are sometimes caused by corrupt tables whose
> page chain has formed a circular loop.

> -bret


> > No I'm using plain old WISQL. I can't even select count(*) from sysaudits.
> > I know that every now and then I need to truncate the table after having
> > copied all records to sysaudits_X, although documentation never says that 20K
> > or 80K is a particular threshold to watch.

> > Anyhow, when I perform select count(*) from sysaudits there are no locks on
> > this table. Upon start, my SELECT process on 11.0.3.3, locks the table with
> > Sh_Intent & Sh_Page and never returns.
> > SELECT count(*) FROM sysaudits_1 returns me the number of rows right away.


> > > 23,000 records is a lot of records to bring back to something like SQL
> > > Advantage. Because you say that you are not being blocked, do a 'set
> > > rowcount 1000' and see if you get anything returned. If you do, then you
> > > know that it is the size of your result set that is the problem. If you
> > > still get nothing back, then I would look again at the locking on your
> > > server.

> > > Andy



> > > > I have about 200MB sybsecurity database with 11.0.3.3, and I activated
> > > > auditing of logins/logouts and errors.
> > > > The sybsecurity is almost empty (about 5% utilization).
> > > > Right now my sysaudits table is about 23K records, sysaudit_1 - 35K.

> > > > However, when I try to select from these tables, it hangs indefinitely.
> > > > I don't see any particular locks on the tables. Maybe I should create
> > > > indexes. I wonder if anyone done this on system tables.
> > > > Why would select be a problem, anyways.



Sun, 26 Jan 2003 03:00:00 GMT
 
 [ 5 post ] 

 Relevant Pages 

1. if you will promise Allahdad's swamp against cans, it will angrily depart the unit

2. sysaudits data - event ids which are not documented

3. Help : trigger on sysaudits

4. Cannot truncate sysaudits table

5. Using sybsecurity.sysaudits table to lock after n number of logins

6. MDX : Canned Report or OLAP

7. Anyone know of some canned (cheap or free) DB performance testing software

8. IF- statements in a rule's 'DO INSTEAD SELECT ...'- statement

9. IF- statements in a rule's 'DO INSTEAD SELECT ...'-

10. canned code to get db on web quickly via perl or

11. IF- statements in a rule's 'DO INSTEAD SELECT ...'- statement

12. Cans access2.0 engine access btrieve files?


 
Powered by phpBB® Forum Software