INF: Bypass (Emergency) Mode and DUMP TRANSACTION WITH NO_LOG

• Microsoft SQL Server versions 7.0, 4.2x, 6.0, 6.5

SUMMARY

In infrequent situations, a database may be marked SUSPECT due to recovery failure at startup time. Normally, this prevents anybody from accessing the data. However, it is possible to manually set the status of a SUSPECT database to "bypass mode" (also called "emergency mode") and SELECT or use the Bulk Copy Program (BCP) to copy out the data. While you cannot do any regular data modifications in bypass mode, it is possible to run DUMP TRANSACTION WITH NO_LOG. Note that doing this operation in bypass mode is unsupported and is a potentially dangerous operation.

For similar reasons, if startup recovery is taking a long time, you should not abort it, set the database in bypass mode, and then do DUMP TRANSACTION WITH NO_LOG.

MORE INFORMATION

All actions taken by DUMP TRANSACTION are usually logged, so it is recoverable and abortable. However, log space is consumed by the DUMP command itself. If the transaction log is so full that insufficient space exists to do a logged DUMP TRANSACTION, the WITH NO_LOG option can truncate the transaction log with no logging.

DUMP TRANSACTION WITH NO_LOG is relatively safe under normal conditions. The server takes measures to ensure that recovery will succeed even if the server fails during this operation.

Under rare circumstances automatic recovery (also called startup recovery) may fail, marking a database SUSPECT. Recovery fails for a specific reason. It is very important to note the errorlog message that initially caused recovery to fail, because it may help to diagnose the cause.

"Recovery" is the process of making the database consistent by redoing or undoing all transactions that were either started after or uncommitted at the time of the last checkpoint. This process relies on the write-ahead nature of the transaction log (all modified pages are written to the log before being written to the database). Recovery consists of reading each log record, comparing its timestamp to the timestamp of the corresponding database page, and either undoing the change (in the case of an uncommitted transaction) or redoing the change (in the case of a committed transaction).

After noting the errorlog message that is causing recovery to fail, try setting the database status back to NORMAL, and restart SQL Server to see if recovery succeeds the second time. You can change the database status by means of the sp_resetstatus stored procedure. This is a supplemental stored procedure you can install from the Instsupl.sql script in the Mssql\Install directory. For more information, see "Resetting the Suspect Status" in the online documentation.

If recovery still fails, note the error message and contact your primary support provider. You should also verify the availability of your last good database backup, because it may be needed. However much of the data in your database is often still available, albeit transactionally (and physically) inconsistent. You can access this data by setting the database status to bypass, or emergency mode. This is done by setting sysdatabases.status to -32768 for the database, after turning "allow updates" on. For example, use the following command:

   UPDATE SYSDATABASES SET STATUS=-32768 WHERE NAME='DBNAME' 

Additional query words:

Keywords : kbusage SSrvGen
Version : winnt:4.2x,6.0,6.5,7.0
Platform : winnt
Issue type : kbinfo