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

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

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: 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: This problem has occurred on computers with multiple CPUs in high stress or high activity situations. After the following command is issued: another connection is gaining access on that database before the following command can be issued: 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.

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.

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: For more information, contact your primary support provider.