FIX: SQLMaint.exe Sets Database Status Incorrectly (276234)


The information in this article applies to:

  • Microsoft SQL Server 7.0 Service Pack 2
This article was previously published under Q276234
BUG #: 58472 (SQLBUG_70)

SYMPTOMS

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.

CAUSE

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.

WORKAROUND

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.

STATUS

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:

http://www.microsoft.com/sql/prodinfo/previousversions/downloads/servicepacks.mspx

For more information, contact your primary support provider.

MORE INFORMATION

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:

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

259551 BUG: Database Remains in Single User Mode w/ DB Maintenance Plan

Modification Type:MajorLast Reviewed:7/19/2006
Keywords:kbbug kbfix KB276234
©2004 Microsoft Corporation. All rights reserved.