SQL: Transaction log maintnance, thresholds

As presented in: SQL: Sybase transaction log, the transaction log is used to store the transactions coming from users. Having this in mind, if the transaction log would become full, no more transactions in the database will be accepted. Database with full transaction log is not usable.

To avoid situation where the transaction log would be full, there is possibility to define thresholds, that define actions that should be started when only transaction log is filled up to some specific level. By default there is Last-Chance Threshold defined. When this threshold is met, automatic process of backup of the log is started.

In production environment, when the option "trunc log on chkpt" is set to OFF, transaction log will be emptied (truncated) only after an incremental backup. Backup of the transaction log is necessary, as the space in the transaction log may be re-used only if the server knows, that the log has been copied already somewhere.

How does it work?


From time to time a checkpoint is issued. Checkpoint performs recovery of the transactions from the log,that have been committed in the moment of the checkpoint. All uncommitted transactions still need to be present in the transaction log as at that time it was still not decided if they will finally be committed or rolled back....

If the option "trunc log on chkpt" would be set to ON (never on production machines) , than Sybase will search for the oldest open transaction. All the space from transaction log before the oldest open transaction is marked for re-use. Unfortunately, if the oldest open transaction is really old, than it is possible, that no space will be freed up.

If the option "trunc log on chkpt " is set to OFF, than just a simple checkpoint is not enough to mark space in transaction log as re-usable. First when a backup will be started and all committed transactions will be copied to the backup, some space from the transaction log may be reused. Again server searches for the oldest open transaction, and as those all already finished transactions are secured in the backup, the space occupied in the log may be marked as reusable. The problem would be again, if a transaction lasts very long! All the checkpoints need to skip that transaction and the backups cannot free the space occupied by it. If the user started a transaction and went to enjoy the lunch, while the other transactions continue the work, this may cause the transaction log to fill up!

As you see it is extremely important to find the oldest open transaction. The following query returns such information:

SELECT * FROM master..syslogshold

If the transaction causes log truncation impossible it should be returned by this query. The output can be similiar to:

With that information it is possible to find the user who caused the problem using sp_who, 

exec sp_who

and if any other solution could not be find - just kill his transaction

kill <spid>

Here also other solutions can be found: https://blogs.sap.com/2013/06/05/ase-faq-my-transaction-log-filled-which-process-filled-up-the-log/

So far, so good. That was an action that needs to be executed after the database's transaction log has filled up. If you are a lucky admin, that should not happen too frequently! As mentioned previously, thresholds can be defined to perform some actions automatically when log is filled up to some level (thresholds can be defined for all types of segments, but using them on transaction log is the most popular).

One threshold is defined in Sybase by default and this is the Last Chance Threshold (LCT). When reached, a procedure named sp_thresholdaction will be started. The bad news is, that this procedure needs to be developed by dba, it is not installed by default. A dedicated procedure may be present at each database, but it is also possible to have one stored in subsystemprocs. When the procedure is called it receives following parameters:

• dbname - the name of the affected database
• segmentname - the name of the segment that is firing the thteshold 
• space_left - the number of pages remaining in the segment mentioned in the previous parameter
• status - flag indicating if the LCThas been reached (=1) or not (=0)

Usually the procedure should write some info into errorlog, it could contact administrator sending for example an email and - what is very important - perform a backup of the transaction log.

The content of the procedure will vary depending on your environment,. This is an example of specialized solution from bossconsulting:

use sybsystemprocs
go

if exists (select * from sysobjects where name = "sp_thresholdaction"
           and type = 'P')
   drop proc sp_thresholdaction
go

create procedure sp_thresholdaction
  @dbname varchar(30),
  @segmentname varchar(30),
  @space_left int,
  @status int
as

declare @controlfile varchar(100)
select @controlfile = "/export/datatools/trans_dump/prod11/"+@dbname

exec backtrack_open_srv...dtsbackup @controlfile, "-log_only"

print "LCT fired through backtrack_open_srv on '%1!'", @dbname 

go

It is also possible to define your own thresholds and corresponding activation procedures for them using the sp_addthreshold

sp_addthreshold dbname, segname, free_space, proc_name