Microsoft KB Archive/259551

= FIX: Database Remains in Single User Mode with the Database Maintenance Plan Option &quot;Repair Any Minor Problems&quot; =

Article ID: 259551

Article Last Modified on 3/14/2006

-

APPLIES TO


 * Microsoft SQL Server 7.0 Standard Edition

-



This article was previously published under Q259551



BUG #: 57804 (SQLBUG_70)



SYMPTOMS
When you run a Database Maintenance Plan with the repair option, any minor problems may leave the database in single user mode.



CAUSE
This problem occurs when a Database Maintenance Plan includes data integrity checks on the indexes and the user has selected the Attempt to repair any minor problems check box. The Maintenance Plan runs the following code if the -CkDBRepair option is specified on the SQLMaint command line: dbcc checkdb(dbname, REPAIR_FAST) WITH NO_INFOMSGS For the REPAIR_FAST option to work, the database needs to be in single user mode. For more information, refer to the following article in the Microsoft Knowledge Base:

247181 FIX: SQLMaint.exe -CkDBRepair Option Does Not Work Correctly

This problem has occurred on computers with multiple CPUs in high stress or high activity situations. After the following command is issued: exec sp_dboption N't1', N'single', N'true' another connection is gaining access on that database before the following command can be issued: exec sp_dboption N't1', N'single', N'false' This occurs because there is no guarantee that the connection that sets a database in single user mode will get into that database first. Therefore, the database is never removed from single user mode.



WORKAROUND
Here are two ways to work around this behavior:
 * Clear the Attempt to repair any minor problems check box.

-or-


 * Manually reset user mode after repairs are attempted.



STATUS
Microsoft has confirmed this to be a problem in SQL Server 7.0. This problem has been corrected in U.S. Service Pack 3 for Microsoft SQL Server 7.0. For more information, click the following article number to view the article in the Microsoft Knowledge Base:

274799 INF: How to Obtain Service Pack 3 for Microsoft SQL Server 7.0 and Microsoft Data Engine (MSDE) 1.0

For more information, contact your primary support provider.

Keywords: kbbug kbfix KB259551

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.