Last-chance threshold

As discussed in previous article, a long database may cause the log to fill up. Then depending on database settings, the transaction causing problem can be aborted (terminated or rolled back) or it can cause that the entire database will be frozen. All transactions will wait until again some free space will be available in the log. Having this in mind, Sybase has mechanism called Last-Chance Threshold. Shortly before filling up the entire log, in the moment when the transactions are aborted or suspended, Sybase starts procedure sp_thresholdaction.

This procedure by default is not present on the system. It needs to be created manually by the administrator. Usually the procedure should create additional TRANSACTION backup, so some space in the log will be made free again and the transactions should be able to continue.  But generally the body of the procedure can contain any actions.

DBA may decide to create more thresholds that will be called depending on conditions met in the log, but there is only one threshold known as "Last Chance".  An example of such procedure below:

USE sybsystemprocs
GO

CREATE PROCEDURE sp_thresholdaction
        @dbname VARCHAR(30),
        @segmentname VARCHAR(30),
        @space_left INT,
        @status INT
AS
    IF @status=1 --last chance thershold
    BEGIN  
        PRINT 'Last threshold condition met...'
        DUMP TRANSACTION @dbname TO dumpsdev
    END
GO

The parameters of the procedure are:

• @dbname - name of the database
• @segmentname - name of the segment for which the procedure is called
• @space_left - amount of 2KB pages
• @status - is 1 for the last-chance threshold; 0 for all other thresholds

If again a transaction would be started filling up the log following sequence of messages should be recorded in errorlog:

tail -f /opt/sap/ASE-16_0/install/SAP01.log

00:0006:00000:00033:2017/03/26 07:35:35.64 server  Space available in segment 'logsegment' has fallen critically low in database 'Beta'. All future modifications to this database will be suspended until the transaction log is successfully dumped and space becomes available.

00:0006:00000:00030:2017/03/26 07:35:35.64 server  background task message: Last threshold condition met...

00:0006:00000:00030:2017/03/26 07:35:35.70 server  Backup Server session id is: 20. Use this value when executing the 'sp_volchanged' system stored procedure after fulfilling any volume change request from the Backup Server.

The entries one by one:

• error message stating that the log space is fast full
• the threshold has been fired and backup has been started
• the backup server sends message about new backup session

As already mentioned it is acceptable to have more thresholds defined. There are also some rules that should be taken into consideration:

• There is a global variable @@thresh_hysteresis, it equals 64. No two threshpoints can be created closer to each another than twice the value of @@thresh_hysteresis
• If a threshold procedure was fired and the amount of free space raised less then the value of @@thresh_hysteresis than the procedure will not be called again

Information regarding defined threshold procedures can be displayed using

sp_helpthreshold

The result can be similar to:

Procedure sp_addthreshold can be used to add your own thresholds to the segment.