INF: Recovering from Full Transaction Log in User Database

ID: Q101479

The information in this article applies to:

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.

After the current database options have been noted, execute the following sequence of SQL commands: 

  /* 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>"
  go

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.

Additional query words: 4.20 Transact-SQL 3414 


Keywords          : kbother SSrvAdmin 
Version           : 4.20
Platform          : OS/2 
Issue type        :

Last Reviewed: March 17, 1999