Archive for May 13th, 2010

CHECKDB and ResourceDB May 13th, 2010

Vinod Kumar

ResourceDB is a new system database that we introduced with SQL Server 2005+. Though this is hidden and cannot be access directly as we used to do with SQL Server 2000 days of changing metadata, there is some protection now. Anyways, there are lots more interesting trivia’s of using ResourceDB and I will reserve it for another day. Today the topic is around – “What if this hidden DB gets corrupt?” … Not exactly, but how can I run CHECKDB on this database?

DBCC CHECKDB (mssqlsystemresource)

This is the obvious choice if you attended my session at TechEd. But interestingly SQL Server throws an error stating –

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.

This is fundamentally because the server is running in multi-server mode. Now there is a neat trick to this, if you try to run the same command against the MASTER database the output looks really interesting. If you scroll carefully through the content, there are some interesting lines not to be missed.

CHECKDB found 0 allocation errors and 0 consistency errors in database ‘master’.
DBCC results for ‘mssqlsystemresource’.
CHECKDB found 0 allocation errors and 0 consistency errors in database ‘mssqlsystemresource’.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

And the ERRORLOG carries an message stating –

DBCC CHECKDB (mssqlsystemresource) executed by Vinod found 0 errors and repaired 0 errors. Elapsed time: 0 hours 0 minutes 1 seconds.

If there is a corruption problem in the resource database and there is a need to get it repaired for the efficient functioning of the SQL Server. As part of your DR plans make sure to keep a copy of the ResourceDB files so that they can be easily brought to an consistent state.To summarize, you will need to replace the resource database if there is any physical corruption introduced in the database. Normally customers will not have the ability to write to this database and a Microsoft Support request needs to be raised.

Continue reading...