Microsoft KB Archive/101479: Difference between revisions
(importing KB archive) |
m (Text replacement - "<" to "<") |
||
(2 intermediate revisions 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: | 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 | ||
Line 85: | Line 85: | ||
The database should now be fully usable and the log truncated. It is STRONGLY advised at this point to perform a full database backup as the transaction log has just been truncated, thus preventing future transaction log dumps from being applied until the database is dumped.<br /> | The database should now be fully usable and the log truncated. It is STRONGLY advised at this point to perform a full database backup as the transaction log has just been truncated, thus preventing future transaction log dumps from being applied until the database is dumped.<br /> | ||
<br /> | <br /> | ||
Note again that the above method is ONLY for the situation where the database has been marked | Note again that the above method is ONLY for the situation where the database has been marked "suspect" by recovery because the transaction log is full. This will be accompanied by SQL Server message 1105. If the database has been marked "suspect" for any other reason, it may be indicative of a serious problem. Contact your primary support provider or drop and recreate the database and restore data from backups.<br /> | ||
<br /> | <br /> | ||
NOTE: This document applies only to 4.2aK6 and earlier. Servers at K7 or greater will not mark the database as suspect. | NOTE: This document applies only to 4.2aK6 and earlier. Servers at K7 or greater will not mark the database as suspect. |
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 |