Microsoft KB Archive/101479: Difference between revisions
m (Text replacement - """ to """) |
m (Text replacement - "<" to "<") |
||
(One intermediate revision by the same user not shown) | |||
Line 48: | Line 48: | ||
go | go | ||
/* status 4112 = 0x1010= | /* status 4112 = 0x1010=>(single user | no checkpoint on recovery) */ | ||
update master..sysdatabases | update master..sysdatabases | ||
set status = 4112 where name = " | set status = 4112 where name = "<database name>" | ||
go </pre> | go </pre> | ||
<br /> | <br /> | ||
Line 62: | Line 62: | ||
restart SQL Server<br /> | restart SQL Server<br /> | ||
<pre class="CODESAMP"> dump tran | <pre class="CODESAMP"> dump tran <database name> with no_log | ||
go </pre> | go </pre> | ||
<br /> | <br /> | ||
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.<br /> | 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.<br /> | ||
<pre class="CODESAMP"> sp_dboption | <pre class="CODESAMP"> sp_dboption <database name>, 'no chkpt', false | ||
go | go | ||
sp_dboption | sp_dboption <database name>, 'single', false | ||
go </pre> | go </pre> | ||
<br /> | <br /> | ||
Reset any options that might have been on in the database like 'select into' before the status field was set to 4112.<br /> | Reset any options that might have been on in the database like 'select into' before the status field was set to 4112.<br /> | ||
<pre class="CODESAMP"> use | <pre class="CODESAMP"> use <database name> | ||
go | go | ||
checkpoint | checkpoint |
Latest revision as of 10:17, 20 July 2020
The information in this article applies to:
SUMMARYWith 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 INFORMATIONThe 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.
/* 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
commit tran go shutdown go
dump tran <database name> with no_log go
sp_dboption <database name>, 'no chkpt', false go sp_dboption <database name>, 'single', false go
use <database name> go checkpoint go sp_configure 'allow', 0 go reconfigure with override go
Keywords : kbother SSrvAdmin Version : 4.20 Platform : OS/2 Issue type : |
Last Reviewed: March 17, 1999 |