I'd like to query your experiences on enabling auditing.

Essentially, the size of the database, your audit configuration, the number
of audit tables used, how quickly the audit tables fill, and whether or not
you make use of the archival database model that is suggested.

Also, what sorts of reports you're extracting from the data.

A case of having a horse and a cart, but verifying that we have the right
type of horse and cart.

Thanks in advance!

On Wed, 01 Sep 2004 23:02:57 -0400, Byrocat wrote:

I use a 2GB database, with two audit tables that rotate automatically
(i.e. no archiving). This covers approx. 5 weeks of activity on my
servers, which is enough for the sort of checking that I need to do
(mainly be able to trace application SQL requests from opaque systems such
as WebObjects when there are performance problems or when inconsistencies
turn up.

Michael
--
Michael Peppler Data Migrations, Inc.
mpeppler (AT) peppler (DOT) org http://www.peppler.org/
Sybase T-SQL/OpenClient/OpenServer/C/Perl developer available for short or
long term contract positions - http://www.peppler.org/resume.html

We had auditing on for "errors", though we turn it off
during the batch shift as it floods audit tables with many
2601 errors (duplicate key). In addition to that, we have
"cmdtext" on for logins used for emergency updates.
We truncate sysaudits tables every week. For this usage,
16 or 32 K per data device plus 4 K for the log device
are enough. If the sysaudits tables don't fill frequently,
just two tables (hence, two sybsecurity data devices) are
enough.

One ugly side effect of auditing is that a new config file
backup is created every time the sysaudits table is switched.
I mean those files as SERVERNAME.172. From time to time,
we have to clean them.

On some servers we added thresholds, combined with xp_cmdshell,
to bcp out a near full sysaudits table, before truncating it.
We moreoless followed the examples in the manual. But we have
referred to those back-ups so rarely than we now think we could
just get rid of the audit records for the full sysaudits table.

Although we are very happy with auditing, we must warn it makes
your server slighty more unstable. First, as you know, every
measure must be taken to ensure that all sysaudits tables aren't
full, or activity may halt. Also, some errors as 1204 (number
of locks exhausted) may kill the internal audit process, and
every app doing any audited activity will get an error; you
must react quickly and run "sp_audit restart" or stop auditing
(for instance, setting the "auditing" config parm to 0).

On the bright side, we have solved some app "misteries" by
looking at the audit records for the "errors" cathegory. Also,
we used "cmdtext" as a desperate debugging tool in the Production
environment; we found it very useful, though risky (sysaudits
tables may fill in minutes)

Regards,
Mariano Corral

"Byrocat" <strikemaster2000 (AT) yahoo (DOT) ca> wrote:

corral (AT) iname (DOT) com (Mariano Corral) wrote in
news:78516de.0409030408.ec98123 (AT) posting (DOT) google.com:

Mariano,

You might wish to run a cron job to clean the files up:

0 2 * * * rm /my-sybase-bits/SERVERNAME.[0-9]*

--
Pablo Sanchez - Blueoak Database Engineering, Inc
http://www.blueoakdb.com