A client came to us with a slightly unusual problem with one of their application databases that would not recover during ASE startup due to the log segment on application database being full.

A log segment full situation is pretty routine in the world of ASE. There are many mechanisms that can be put in place to minimize them but a rogue new query, or quite possible a data driven query gone awry can strike without warning. And it is possible that when the log segment becomes too full, a Tran Dump may not even be possible to clear it.

Mechanisms such as database options ‘truncate log on checkpoint’ (if acceptable), ‘abort tran on log full’ (if acceptable), and/or frequent Tran Dumps of the database are a great start not just for recoverability, but to clear the log segment of committed data no longer needed. They are a good start but can still leave the transaction log vulnerable.

In this situation, that was just the case with one caveat, the ASE instance was shutdown while the log segment was full. Then when the ASE was restarted and the database was going through normal startup recovery, it again reported the log segment was full and the database would not come online. It is one of those ‘panic’ moments. The ASE is producing tons of messages, the database will not come online, normal actions like dump transaction or dump transaction with truncate_only or even the infamous dump transaction with no_log will not work, and space cannot be added to the log segment because the database is still undergoing recovery.

There are however steps that can be taken to get the database to a state that space can be added to the log segment and get the database back online. The database can be set to not recover on startup which will allow adding space to the log segment of the database.

Bypass Recovery Procedure:

A word of CAUTION:

These steps should be considered a last-ditch effort to get a database to recover at startup for this situation only. This should not be considered normal procedure.  Further investigation as to the cause of the log segment filling should be done as to not get to this point in the first place. See the Conclusion below.

Before you begin:

  • Identify a database device already in place that is suitable to extend the log segment onto. If one is not available, a device will have to be created (disk init) to a suitable size.
  • Also, this procedure will require rebooting the ASE at least 2 times. Notify and plan appropriately before you start.
  • Make a note of the current setting of database options. Also make note of the ‘status’ value for the database in sysdatabases:

select name, status from master..sysdatabases where name = <"name of database">
go

Get current database options set to be re-applied or verified later.

sp_helpdb <dbname>

go

  • Set the database status to bypass recovery:

sp_configure "allow updates", 1
go

begin tran
go

update master..sysdatabases set status = -32768 where name = <"name of database">
go

/* Ensure only 1 row is affected before running the COMMIT! */
commit tran
go

shutdown
go

  • Restart ASE. This database will not undergo startup recovery.
     
  • Increase the database log size. This can be done to an offline database:

alter database <database> log on <log device> = <amount of space to increase>
go

  • For example: To alter the database transaction log segment for 50M:

alter database <dbname> log on <log_device> = ‘50M’
go

  • Once the transaction log has been increased, set the database status to what it was before:

begin tran
go

update sysdatabases set status = <previous value> where name = <"name of database">
go

/* Ensure only 1 row is affected before running the COMMIT! */
commit tran
go

shutdown
go

  • Restart ASE. The database should now recovery normally.
  • on checkpoint", "ddl in tran".

sp_dboption <database>, "<database option>", true
go

use <database>
go

checkpoint
go

sp_configure "allow updates", 0
go

  • It is very important to take a full database backup of the affected database, and the master database as soon as possible! This ensures the log segment increase to both the database, and changes to the master database are backed up.

Conclusion:

Further investigation found they were doing regular tran dumps, but the tran dumps were taking a long time to complete and were sometimes overlapping executions. This warranted investigation of the location the backup was being written to see if there was I/O latency. A transaction log should not get so large that it takes a long time to take a tran log dump.

It was also time to implement other mechanisms that are not always in place. Namely thresholds on the log segment. Threshold can be placed on a log segment to executes a stored procedure when the number of available pages in the logs segment reaches a threshold (specified in number of pages).  The stored procedure can do things like write a message to the error log, attempt to dump the transaction log, even kill selected processes to free up log segment space. When in a database the ‘sp_helpthreshold’ command will show what thresholds are set.

There are very nice write-ups with some elaborate threshold procedure samples in the SAP ASE System Administration Guide Vol. 2 – Transaction Log Space Management section.