BUG: Database maintenance plan on system databases fails on integrity check if "Attempt to repair minor problems" is selected
This article was previously published under Q290622
This article has been archived. It is offered "as is" and will no longer be updated.
BUG #: 352452 (SHILOH)
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.  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.
The database maintenance plan tries to put the system databases in the single-user mode in order to execute -CkDBRepair. However, the master and msdb databases cannot be put in single-user mode because of constantly running system processes such as LAZYWRITER, LOG WRITER, CHECKPOINT, SQLAgent and Generic Refresher. The repair step fails to run, and therefore the job reports a failure.
Note This problem is not limited to system databases. It 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.
Disable the Attempt to repair any minor problems option. To do this, follow these steps in the SQL Server Enterprise Manager:
- Right-click and select Properties on the Database Maintenance plan.
- Click the Integrity tab.
- Click to clear the Attempt to repair any minor problems check box under Check Database Integrity.
Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.
Article ID: 290622 - Last Review: 12/06/2015 00:52:31 - Revision: 5.0
Microsoft SQL Server 2000 Enterprise Edition, Microsoft SQL Server 2000 Developer Edition, Microsoft SQL Server 2000 Standard Edition
- kbnosurvey kbarchive kbbug kbfaq kbpending KB290622