You are currently offline, waiting for your internet to reconnect

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

This article was previously published under Q165918
Retired KB Content Disclaimer
This article was written about products for which Microsoft no longer offers support. Therefore, this article is offered "as is" and will no longer be updated.
In infrequent situations, a database may be marked SUSPECT due to recoveryfailure at startup time. Normally, this prevents anybody from accessing thedata. However, it is possible to manually set the status of a SUSPECTdatabase to "bypass mode" (also called "emergency mode") and SELECT or usethe Bulk Copy Program (BCP) to copy out the data. While you cannot do anyregular data modifications in bypass mode, it is possible to run DUMPTRANSACTION WITH NO_LOG. Note that doing this operation in bypass mode isunsupported and is a potentially dangerous operation.

For similar reasons, if startup recovery is taking a long time, you shouldnot abort it, set the database in bypass mode, and then do DUMP TRANSACTIONWITH NO_LOG.
All actions taken by DUMP TRANSACTION are usually logged, so it isrecoverable and abortable. However, log space is consumed by the DUMPcommand itself. If the transaction log is so full that insufficient spaceexists to do a logged DUMP TRANSACTION, the WITH NO_LOG option can truncatethe 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 theserver 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 causedrecovery to fail, because it may help to diagnose the cause.

"Recovery" is the process of making the database consistent by redoing orundoing all transactions that were either started after or uncommitted atthe time of the last checkpoint. This process relies on the write-aheadnature of the transaction log (all modified pages are written to the logbefore being written to the database). Recovery consists of reading eachlog record, comparing its timestamp to the timestamp of the correspondingdatabase page, and either undoing the change (in the case of an uncommittedtransaction) or redoing the change (in the case of a committedtransaction).After noting the errorlog message that is causing recovery to fail, trysetting the database status back to NORMAL, and restart SQL Server to seeif recovery succeeds the second time. You can change the database status bymeans of the sp_resetstatus stored procedure. This is a supplemental storedprocedure you can install from the Instsupl.sql script in the Mssql\Installdirectory. For more information, see "Resetting the Suspect Status" in theonline documentation.

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

After doing this, you can enter the database and SELECT the data or use BCPto get it out. You may encounter errors while doing this, but in most casesmuch of the data can be retrieved.

Article ID: 165918 - Last Review: 02/22/2005 22:39:51 - Revision: 3.1

  • Microsoft SQL Server 4.21a Standard Edition
  • Microsoft SQL Server 6.0 Standard Edition
  • Microsoft SQL Server 6.5 Standard Edition
  • kbinfo kbusage KB165918