You are currently offline, waiting for your internet to reconnect

FIX: Database Remains in Single User Mode with the Database Maintenance Plan Option "Repair Any Minor Problems"

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.
Properties

Article ID: 259551 - Last Review: 03/14/2006 10:31:15 - Revision: 2.2

  • Microsoft SQL Server 7.0 Standard Edition
  • kbbug kbfix KB259551
Feedback