The Audit Queue
ASE collects audit records, but does not store these records directly into the
sysaudit tables. Auditing records are temporarily stored in the audit queue,
which will hold auditing records until they are flushed to disk. Unfortunately,
if there is a system crash, any records in the audit queue will be lost since it is
memory resident. To help reduce the number of records that could be lost as a
result of system crash, the DBA can adjust the size of the audit queue with
NOTE: Audit queue size configuration is dynamic.
When attempting to configure the size of the audit queue, the DBA should
consider the trade-offs. Making the audit queue too large can result in losing
more audit records in the event of a system crash. Making the audit queue too
small can negatively impact system performance, since the audit queue will
repeatedly fill up. When the audit queue is full, a process that has generated
an audit record will sleep until space becomes available within the audit
Therefore, it is your job as the SSO to decide upon the correct size of the
audit queue. By default, the audit queue is set to 100 records. In order to prop-
erly set the size of the audit queue, you might want to start small, and attempt
to determine if the audit process is a bottleneck from the frequent flushing of
the audit queue. Keep increasing the size until the performance reaches an
acceptable level while balancing against the number of records lost in event
of system failure. Generally, if security is your main concern, keep the audit
queue smaller; if performance is your main concern, set the audit queue
The syntax for altering the size of the audit queue:
sp_configure 'audit queue size', num_of_audit_records
An example of configuring the audit queue to store 20 audit records:
sp_configure 'audit queue size', 20
Audit Database Storage Requirements
The number of audit records that can be kept in the sysaudits tables is limited
by the size of the sybsecurity database. Audit records will continue to fill the
sysaudits tables until the SSO disables auditing; each audit record requires
from 32 to 424 bytes. As you can expect, the higher the amount of activity on
168 Chapter 7: Auditing
your system, combined with the number of events being audited, the faster
the sybsecurity database will fill.
To help size the database, the DBA needs to know the number of audit
records that will be generated, the size of the audit records, and the amount of
time until audit records will be purged or archived from the database. The
general formula is:
sybsecurity size = Audit record size * number of audit records
* time in DB for the r ecorded event
Of course, in order to correctly size the database, you need to take a sample of
the auditing activity. However, this is impossible before you set up the data
base and install auditing. One recommendation would be to set up an auditing
database, save all configuration settings in script format, take several samples
of audit activity, then alter the size of the database to reflect your volume of
auditing information and time between truncation or archiving.
Be careful not to configure the database too large for a system without
much activity, because you cannot easily shrink a database! If you find that
the database size is too large, and you need to allocate the devices associated
with your sybsecurity database to other resources, then be sure to follow the
above recommendation and save all audit configuration settings to a script
before dropping the database and rebuilding.
The volume of activity in your audit database can be determined by fol-
lowing the steps outlined below:
1. Install auditing.
2. Configure audit options.
3. If you already have run an audit, ensure the audit tables have zero records
by truncating the tables, or make note of the space already used.
4. Enable audit options with sp_audit.
5. Enable auditing for a selected time period.
sp_configure "auditing", 1
6. Disable auditing after a selected time period.
sp_configure "auditing", 0
7. Evaluate the space usage of the audit tables with sp_spaceused or the
name rowtotal reserved data index_size unused
------------ -------- -------- ------ ---------- ------
sysaudits_01 979 142 KB 130 KB 0 KB 12 KB
(1 row affected, return status = 0)
Chapter 7: Auditing