Transaction log - How to measure the growth

The availability of a Sybase database is highly dependent on free space within the transaction log. When there is no free space left a database can go into "log suspend" mode, or an sp_thresholdaction stored procedure must finish before transaction processing can continue. This technical tip includes a stored procedure that prints a message in the server's errorlog and a technique to warn you when free space drops below a certain point. For instance, when the free space within the transaction log segment drops below 30%, you will see a warning in the errorlog. With this information you can adequately size the transaction log or make changes to your backup strategy.

An alternative method, not described here, is to place several thresholds at for instance 70%, 80% and 90% fullness of the transactionlog that dump it. For production systems this is the preferred method to avoid hitting the last change threshold. Both methods can be combined.

Software download link: http://www.petersap.nl/documents/logmonitor.zip (6 Kb)

A brief description of segments and thresholds

Each database has a transaction log located in the syslogs system table. This table is always placed on the segment named "logsegment". Segments can be placed on devices that are allocated to the database. You can use the "sp_helpdb <name of database>" stored procedure to see the devices used by a certain database. The sp_helpsegment stored procedure shows the placement of a segment on those devices. When you want to see the size of a transaction log or on which devices it is located, you can use "sp_helpsegment logsegment" from that database.

Thresholds are used as a kind of 'triggers' on segments and are activated when the free space within a segment drops below a certain point. A threshold can be set by a DBA and be associated with a user defined stored procedure.

(Pictures from Sybase SA manual)

Measure the growth of the transaction log

Since the transactionlog is located on the logsegment we can place a number of thresholds on this segment. When the free space within the logsegment drops below a certain point the stored procedure associated with the threshold is activated and a message is printed in the Sybase errorlog. When the transaction log regularly reaches a certain percentage of used space, a DBA can decide to increase the size of the transaction log, or to make more frequent dumps of it. As a rule of thumb, I aim at keeping at least 20% of free space within the transaction log. Databases that a part of a replication system might need more free space, to overcome problems when the replication server is temporarily down.

The software (download link: http://www.petersap.nl/documents/logmonitor.zip (6 Kb) consists of two parts: first a sample stored procedure and secondly a Unix script to install the threshold in the databases on a server.

Installation

The installation is a two step process:

• install the sp_tranlogusage stored procedure into the sybsystemprocs database
• run the install.sh script. The script installs the thresholds in every database, except model.

Feel free to modify the script to exclude databases. The variable PERCENTAGE_LIST within the script is used to define the placements of thresholds.

De-installation

• drop the sp_tranlogusage stored procedure from the sybsystemprocs database
• rerun the install.sh script. The script removes the thresholds associated with the sp_tranlogusage stored procedure, but does not set new thresholds because the stored procedure doesn't exist.

Maintenance

This system is basically maintenance free, except for the following:

• when you extend the logsegment the thresholds are not moved and this causes a delay in reaching a threshold. For instance, a threshold previously set at 20 % free space might be moved to 15 % of free space after the logsegment has been extended. This problem can be overcome by re-installing the thresholds by running the install.sh script again.
• when a new database is added to the server, you must install thresholds in it by running the script again.

Possible conflicts with other thresholds

A threshold can only be set on a certain segment and a point within that segment when no other threshold is at or near that point. When you have already installed some additional thresholds on a logsegment the addition of the extra thresholds needed for the sp_tranlogusage may fail. Your existing thresholds are never removed or overwritten by the procedure or script. As a solution for this you can implement your own threshold procedures that combine the functionality you already have and the method described in this document.