FIX: SQLMaint.exe Sets Database Status Incorrectly

When attempting to repair minor errors on databases, the Database Maintenance Plan Wizard puts the databases in single-user mode and may not reset their status to regular (multi-user) mode.

In SQL Server 7.0 Service Pack 2, the Database Maintenance Plan Wizard places a database in single user mode when the option to attempt to repair any minor problems is selected in the maintenance plan. If the maintenance job fails for any reason, databases may be left in single-user mode.

To work around this problem, do either of the following:

• Add a step in the job to force the database out of single-user mode after the job completes. Database maintenance plans automatically generate jobs, and a particular job will be named with the name of the maintenance plan followed by 'DB Maintenance Plan check Integrity'. After the creation of the job, you could edit it to insert a step to set the single-user mode option to false after the existing steps.
  
  -or-
• Manually remove the Single-User option of the database, by using either Enterprise Manager or the sp_dboption stored procedure.

Microsoft has confirmed this to be a problem in SQL Server 7.0 Service Pack 2. This problem has been corrected in U.S. Service Pack 3 for Microsoft SQL Server 7.0 Service Pack 2. For information about how to download and install the latest SQL Server Service Pack, see the following Microsoft Web site: For more information, contact your primary support provider.

Steps to Reproduce the Problem

1. In Enterprise Manager, navigate to the Databases folder and click it to select it.
2. Click the Run a Wizard button on the toolbar (or on the Tools menu, click Wizards). The Select Wizard dialog box appears. Expand Management by clicking the plus sign (+).
3. Click Database Maintenance Plan Wizard and then click OK.
4. Read the Welcome message and then click Next to proceed to the Select Databases screen.
5. Click All Databases and then click Next.
6. Do not select anything in the Update Data Optimization Information section; click Next.
7. Click Check Database Integrity and then click Attempt to Repair Any Minor Problems. To schedule the maintenance plan to run every minute, click Change at the bottom of the dialog box and then set the appropriate schedule. After setting the schedule, click Next.
8. Click to clear all the default values from the check boxes on the next screen and click Next.
9. Make sure nothing is selected on the Specify the Transaction Log Backup Plan screen and click Next.
10. Make sure nothing is selected on the Reports to Generate screen and click Next.
11. Make sure nothing is selected on the Maintenance History screen and click Next.
12. Give the Maintenance plan a name, such as "Plan 1" and then click Finish.
13. Under the Management folder in Enterprise Manager, expand SQL Server Agent and then click Jobs.
14. Right-click Integrity Checks Job for DB Maintenance Plan 'Plan 1' and then click Start Job on the shortcut menu.
15. While the job is running, right-click the Databases folder in Enterprise Manager and click Refresh on the shortcut menu.

For additional information on this problem, click the article numbers below to view the articles in the Microsoft Knowledge Base: