https://www.betaarchive.com/wiki/index.php?title=Microsoft_KB_Archive/165918&feed=atom&action=history
Microsoft KB Archive/165918 - Revision history
2024-03-28T08:56:05Z
Revision history for this page on the wiki
MediaWiki 1.39.3
https://www.betaarchive.com/wiki/index.php?title=Microsoft_KB_Archive/165918&diff=340796&oldid=prev
X010: Text replacement - """ to """
2020-07-21T10:02:19Z
<p>Text replacement - """ to """</p>
<table style="background-color: #fff; color: #202122;" data-mw="interface">
<col class="diff-marker" />
<col class="diff-content" />
<col class="diff-marker" />
<col class="diff-content" />
<tr class="diff-title" lang="en-GB">
<td colspan="2" style="background-color: #fff; color: #202122; text-align: center;">← Older revision</td>
<td colspan="2" style="background-color: #fff; color: #202122; text-align: center;">Revision as of 10:02, 21 July 2020</td>
</tr><tr><td colspan="2" class="diff-lineno" id="mw-diff-left-l46">Line 46:</td>
<td colspan="2" class="diff-lineno">Line 46:</td></tr>
<tr><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div>== SUMMARY ==</div></td><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div>== SUMMARY ==</div></td></tr>
<tr><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><br/></td><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><br/></td></tr>
<tr><td class="diff-marker" data-marker="−"></td><td style="color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #ffe49c; vertical-align: top; white-space: pre-wrap;"><div>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 <del style="font-weight: bold; text-decoration: none;">&quot;</del>bypass mode<del style="font-weight: bold; text-decoration: none;">&quot; </del>(also called <del style="font-weight: bold; text-decoration: none;">&quot;</del>emergency mode<del style="font-weight: bold; text-decoration: none;">&quot;</del>) 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.<br /></div></td><td class="diff-marker" data-marker="+"></td><td style="color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #a3d3ff; vertical-align: top; white-space: pre-wrap;"><div>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 <ins style="font-weight: bold; text-decoration: none;">"</ins>bypass mode<ins style="font-weight: bold; text-decoration: none;">" </ins>(also called <ins style="font-weight: bold; text-decoration: none;">"</ins>emergency mode<ins style="font-weight: bold; text-decoration: none;">"</ins>) 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.<br /></div></td></tr>
<tr><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div><br /></div></td><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div><br /></div></td></tr>
<tr><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div>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.</div></td><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div>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.</div></td></tr>
<tr><td colspan="2" class="diff-lineno" id="mw-diff-left-l61">Line 61:</td>
<td colspan="2" class="diff-lineno">Line 61:</td></tr>
<tr><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div>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.<br /></div></td><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div>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.<br /></div></td></tr>
<tr><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div><br /></div></td><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div><br /></div></td></tr>
<tr><td class="diff-marker" data-marker="−"></td><td style="color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #ffe49c; vertical-align: top; white-space: pre-wrap;"><div><del style="font-weight: bold; text-decoration: none;">&quot;</del>Recovery<del style="font-weight: bold; text-decoration: none;">&quot; </del>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 <del style="font-weight: bold; text-decoration: none;">&quot;</del>Resetting the Suspect Status<del style="font-weight: bold; text-decoration: none;">&quot; </del>in the online documentation.<br /></div></td><td class="diff-marker" data-marker="+"></td><td style="color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #a3d3ff; vertical-align: top; white-space: pre-wrap;"><div><ins style="font-weight: bold; text-decoration: none;">"</ins>Recovery<ins style="font-weight: bold; text-decoration: none;">" </ins>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 <ins style="font-weight: bold; text-decoration: none;">"</ins>Resetting the Suspect Status<ins style="font-weight: bold; text-decoration: none;">" </ins>in the online documentation.<br /></div></td></tr>
<tr><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div><br /></div></td><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div><br /></div></td></tr>
<tr><td class="diff-marker" data-marker="−"></td><td style="color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #ffe49c; vertical-align: top; white-space: pre-wrap;"><div>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 <del style="font-weight: bold; text-decoration: none;">&quot;</del>allow updates<del style="font-weight: bold; text-decoration: none;">&quot; </del>on. For example, use the following command for a SQL 6.5 database:<br /></div></td><td class="diff-marker" data-marker="+"></td><td style="color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #a3d3ff; vertical-align: top; white-space: pre-wrap;"><div>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 <ins style="font-weight: bold; text-decoration: none;">"</ins>allow updates<ins style="font-weight: bold; text-decoration: none;">" </ins>on. For example, use the following command for a SQL 6.5 database:<br /></div></td></tr>
<tr><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><br/></td><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><br/></td></tr>
<tr><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div><pre class="codesample"> UPDATE SYSDATABASES SET STATUS=-32768 WHERE NAME='DBNAME'</div></td><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div><pre class="codesample"> UPDATE SYSDATABASES SET STATUS=-32768 WHERE NAME='DBNAME'</div></td></tr>
</table>
X010
https://www.betaarchive.com/wiki/index.php?title=Microsoft_KB_Archive/165918&diff=86266&oldid=prev
3155ffGd: importing KB archive
2020-07-18T15:32:31Z
<p>importing KB archive</p>
<p><b>New page</b></p><div><div id="nsbanner"><br />
<br />
<div id="bannerrow1"><br />
<br />
{|<br />
| Knowledge Base<br />
|<br />
|}<br />
<br />
<br />
</div><br />
<div id="TitleRow"><br />
<br />
= <span id="KB165918"></span>INF: Bypass (Emergency) Mode and DUMP TRANSACTION WITH NO_LOG =<br />
<br />
<br />
</div><br />
<br />
</div><br />
<div id="nstext" valign="BOTTOM"><br />
<br />
Article ID: 165918<br />
<br />
Article Last Modified on 2/22/2005<br />
<br />
<br />
-----<br />
<br />
APPLIES TO<br /><br />
<br /><br />
<br />
* Microsoft SQL Server 4.21a Standard Edition<br />
* Microsoft SQL Server 6.0 Standard Edition<br />
* Microsoft SQL Server 6.5 Standard Edition<br />
<br />
<br />
-----<br />
<br />
<div class="notice_section"><br />
<br />
This article was previously published under Q165918<br />
<br />
</div><br />
<div class="summary_section"><br />
<br />
== SUMMARY ==<br />
<br />
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 &quot;bypass mode&quot; (also called &quot;emergency mode&quot;) 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.<br /><br />
<br /><br />
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.<br />
<br />
</div><br />
<div class="moreinformation_section"><br />
<br />
== MORE INFORMATION ==<br />
<br />
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.<br /><br />
<br /><br />
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.<br /><br />
<br /><br />
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.<br /><br />
<br /><br />
&quot;Recovery&quot; 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 &quot;Resetting the Suspect Status&quot; in the online documentation.<br /><br />
<br /><br />
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 &quot;allow updates&quot; on. For example, use the following command for a SQL 6.5 database:<br /><br />
<br />
<pre class="codesample"> UPDATE SYSDATABASES SET STATUS=-32768 WHERE NAME='DBNAME'<br />
</pre><br />
<br /><br />
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.<br />
<br />
</div><br />
Keywords: kbinfo kbusage KB165918<br />
<br />
<div class="footer"><br />
<br />
<br /><br />
<br />
<br />
-----<br />
<br />
[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]<br />
<br />
<span>© Microsoft Corporation. All rights reserved.</span><br />
<br />
<br />
</div><br />
<br />
</div></div>
3155ffGd