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

Article translations Article translations
Article ID: 259551 - View products that this article applies to.
This article was previously published under Q259551
BUG #: 57804 (SQLBUG_70)
Expand all | Collapse all

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: March 14, 2006 - Revision: 2.2
APPLIES TO
  • Microsoft SQL Server 7.0 Standard Edition
Keywords: 
kbbug kbfix KB259551

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com