Wednesday, February 3, 2010

DBCC CHECKDB() on ResourceDB?

A few days ago I was answering a forum post about CHECKDB() and as part of the research I checked the Windows Application Log.  I was surprised to see this entry:

Event Type:    Information
Event Source:    MSSQLSERVER
Event Category:    (2)
Event ID:    8957
Date:        1/30/2010
Time:        5:45:02 PM
User:        domain\username
Computer:    ServerName
DBCC CHECKDB (mssqlsystemresource) WITH all_errormsgs, no_infomsgs, data_purity executed by domain\username found 0 errors and repaired 0 errors. Elapsed time: 0 hours 0 minutes 0 seconds.

I’m using Ola Hallengren’s backup and maintenance scripts to do my integrity checks, but you can’t run DBCC CHECKDB() against ResourceDB, can you?  Well, just to verify I tried it and got this message:

Msg 2520, Level 16, State 11, Line 1
Could not find database 'mssqlsystemresource'. The database either does not exist, or was dropped before a statement tried to use it. Verify if the database exists by querying the sys.databases catalog view.

So where did the application log come from?  As usual BOL has the answer in the DBCC CHECKDB() entry:

Because the Resource database is modifiable only in single-user mode, the DBCC CHECKDB command cannot be run on it directly. However, when DBCC CHECKDB is executed against the master database, a second CHECKDB is also run internally on the Resource database. This means that DBCC CHECKDB can return extra results.

So while you can’t directly run the command SQL Server is taking care of it for you when you run it against the master database.

The question is, are you doing anything to BACKUP the Resource Database

No comments:

Post a Comment

So what do you think I am?