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

Article translations Article translations
Article ID: 165918 - View products that this article applies to.
This article was previously published under Q165918
Expand all | Collapse all

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 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:
   UPDATE SYSDATABASES SET STATUS=-32768 WHERE NAME='DBNAME'
				

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

Properties

Article ID: 165918 - Last Review: February 22, 2005 - Revision: 3.1
APPLIES TO
  • Microsoft SQL Server 4.21a Standard Edition
  • Microsoft SQL Server 6.0 Standard Edition
  • Microsoft SQL Server 6.5 Standard Edition
Keywords: 
kbinfo kbusage KB165918
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.

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com