Check Integrity task in Maint Plan fails on SQL Server 2000


When a SQL Server 2000 Database Maintenance Plan is executed, you find that the following message in printed in the Maint Plan .OUT file (Found in the SQL Server LOG folder):
 
The backup was not performed since data verification errors were found
 
OR
 
[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.
 

 
This happens because you had selected the option of "Attempt to repair any minor problems" (refer above pic). This option needs CHECKDB to be executed with the database in SINGLE USER mode. If you have users connected to the database during that time, the database cannot be put into SINGLE USER mode due to which the Integrity Check task will fail. This will prevent backups from occurring on the database and you would see the above messages in the log files.
 
DBCC CHECKDB (<database name>, repair_fast) executed by <account name> found 0 errors and repaired 0 errors.  Elapsed time: 0 hours 0 minutes 50 seconds.
 
If you look in the SQL Server ERRORLOG, you will find that the CHECKDB is executed with REPAIR_FAST flag. This requires Single User mode. Another note to keep in mind is if you check the Integrity Check option and "Attempt to repair any minor problems", then exclude MASTER, MSDB and MODEL databases from the Plan.
 
Reference Article:
290622 BUG: Database maintenance plan on system databases fails on integrity check if "Attempt to repair minor problems" is selected
http://support.microsoft.com/default.aspx?scid=kb;EN-US;290622
Advertisements

It is always good to hear from you! :)

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s