Sybase code sample: How to measure the growth of the transaction log

The availability of a Sybase database is highly dependent on free space within the transaction log.
When there is no free space left a transaction can go into "log suspend" mode, or a 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.

Content 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”.
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.

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 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:
1) install the sp_tranlogusage stored procedure into the sybsystemprocs database
2) 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
De-installation is quite simple:
1) drop the sp_tranlogusage stored procedure from the sybsystemprocs database
2) 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.

Sign up to receive technical tips and how-to's regularly.
Please enter your e-mail address below:
E-mail address: (required)

Disclaimer.