INF: Recovering from Full Transaction Log in User Database

  - Microsoft SQL Server version 4.2 for OS/2

SUMMARY

With SQL Server versions earlier than 4.2aK7, database recovery at SQL Server startup requires that a CHECKPOINT record be written to each database. Should the transaction log of a user database become so full that a checkpoint record cannot be written the database owning the full transaction log will be marked suspect by the recovery process. This document describes the procedure to recover a database which has been marked as suspect due to this problem.

MORE INFORMATION

The following documented procedure should ONLY be used for dumping the log of a full USER database. Do NOT use it for any other reason! Remember, if the log fills up at runtime, you can issue a DUMP TRAN WITH NO_LOG without going through any of the following procedure. This procedure cannot be used to recover a master database.

The correct procedure for dumping the log in a full USER database is:

   Start SQL Server as usual. The database that has the full transaction
   log will be marked suspect by recovery. Make a note of the user
   defined options that were set for this database as they will have to
   be reset when this procedure has been completed. These options can be
   examined by issuing the SP_HELPDB stored procedure and specifying the
   desired database as the first parameter.

> /* allow updates to system tables */ > sp_configure 'allow', 1 > go > > /* causes sp_configure to take effect */ > reconfigure with override > go > > begin tran > go > > /* status 4112 = 0x1010=>(single user | no checkpoint on recovery) */ > update master..sysdatabases

>     set status = 4112 where name = "<database name>"

Check that the above command affected only one row. If not, issue a ROLLBACK and start over. If so, execute the following commands:

> commit tran > go > shutdown > go

restart SQL Server

> dump tran <database name> with no_log > go

At this point you will get error 924: "db X is already open and can only have one user at a time...." Ignore this message, the dump has taken place.

> sp_dboption <database name>, 'no chkpt', false > go > sp_dboption <database name>, 'single', false > go

Reset any options that might have been on in the database like 'select into' before the status field was set to 4112.

> use <database name> > go > checkpoint > go > sp_configure 'allow', 0 > go > reconfigure with override > go

The database should now be fully usable and the log truncated. It is STRONGLY advised at this point to perform a full database backup as the transaction log has just been truncated, thus preventing future transaction log dumps from being applied until the database is dumped.

Note again that the above method is ONLY for the situation where the database has been marked "suspect" by recovery because the transaction log is full. This will be accompanied by SQL Server message 1105. If the database has been marked "suspect" for any other reason, it may be indicative of a serious problem. Contact your primary support provider or drop and recreate the database and restore data from backups.

NOTE: This document applies only to 4.2aK6 and earlier. Servers at K7 or greater will not mark the database as suspect.