Microsoft KB Archive/290622

= BUG: Database maintenance plan on system databases fails on integrity check if &quot;Attempt to repair minor problems&quot; is selected =

Article ID: 290622

Article Last Modified on 12/29/2005

-

APPLIES TO


 * Microsoft SQL Server 2000 Standard Edition

-



This article was previously published under Q290622



BUG #: 352452 (SHILOH)



SYMPTOMS
When the Attempt to repair any minor problems option is enabled, a database maintenance plan may fail when you run integrity checks on all the system databases and you receive the following error message:

[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 5058: [Microsoft][ODBC SQL Server Driver][SQL Server]Option 'SINGLE_USER' cannot be set in database 'MASTER'. [Microsoft][ODBC SQL Server Driver][SQL Server]sp_dboption command failed. [1] Database master: Check Data and Index Linkage... [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 7919: [Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement not processed. Database needs to be in single user mode.



CAUSE
The database maintenance plan attempts to place the system databases in the single user mode in order to execute -CkDBRepair. However, the master and the msdb databases cannot be placed in single user mode because of constantly running system processes like LAZYWRITER, LOG WRITER, CHECKPOINT, SQLAgent and Generic Refresher. The repair step fails to run and the job reports a failure.

Note This problem is not limited to system databases. This also occurs with user databases if users are connected to the databases when the maintenance plan tries to set the database to single user mode.



WORKAROUND
Disable the Attempt to repair any minor problems option. To disable the option, perform these steps in the SQL Server Enterprise Manager:
 * 1) Right-click and choose Properties on the Database Maintenance plan.
 * 2) Click the Integrity tab.
 * 3) Clear the Attempt to repair any minor problems check box under Check Database Integrity.



STATUS
Microsoft has confirmed this to be a problem in SQL Server 2000.

Keywords: kbbug kbfaq kbpending KB290622

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.