If you use db_option to turn the trunc log on chkpt off, the transaction log may fill up. Plan to attach a last-chance threshold procedure to the transaction log segment. This procedure gets control when the amount of space remaining on the segment is less than a threshold amount computed automatically by Adaptive Server. The threshold amount is an estimate of the number of free log pages that would be required to back up the transaction log.
The default name of the last-chance threshold procedure is sp_thresholdaction, but you can specify a different name with sp_modifythreshold, as long as you have the sa_role active.
sp_modifythreshold checks
to ensure you have “sa_role” active.
See “Attaching the threshold procedure to each audit segment” for
more information.
Adaptive Server does not supply a default procedure, but Chapter 29, “Managing Free Space with Thresholds” contains examples of last-chance threshold procedures. The procedure should execute the dump transaction command, which truncates the log. When the transaction log reaches the last-chance threshold point, any transaction that is running is suspended until space is available. The suspension occurs because the option abort xact when log is full is always set to FALSE for the sybsecurity database. You cannot change this option.
With the trunc log on chkpt option off, you can use standard backup and recovery procedures for the sybsecurity database, but be aware that the audit tables in the restored database may not be in sync with their status at the time of a device failure.
