Some of the simplest recommendations can lead you to some bizarre output that we never anticipated. I always recommend running DBCC CHECKDB on mission critical databases because we need to be 200% sure that the database disk system is healthy from time to time. During one of my sessions, I gave this as a generic recommendation and guess what, I got a strange message from one of the attendees. They then claimed that they were getting errors of mag 2520. One further probing and requesting for the actual error, I understood the problem. They were running CHECKDB on the ResourceDB. I asked – “Why are you doing this on ResourceDB?”. They answered, “Pinal, isn’t ResourceDB a database? Wouldn’t it not get corrupt? What is wrong in doing a CHECKDB on it? If I cant, how can I do it?”
This was a fully loaded question and I had to answer it elaborately. So I took this blog post to explain some of the basics.
Resource database is a system database in SQL Server 2005. Since this database has some special attributes and differs from regular databases in some respects, these indirectly affect the ability to do some operations on this database. One of the areas is the ability to perform DBCC checks and repair on the resource database. The following are the key points to keep in mind when you plan to run any kind of check against the resource database:
When the server is running in multi-user mode, running the command
DBCC CHECKDB ( mssqlsystemresource )
will give the following output:
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 applicable for commands like DBCC CHECKALLOC and DBCC CHECKCATALOG.
When the server is running in multi-user mode, running DBCC CHECKDB on the master database will run DBCC CHECKDB on resource database as well. In the DBCC CHECKDB output result set, we will notice the following sections:
DBCC CHECKDB ( MASTER )
CHECKDB found 0 allocation errors and 0 consistency errors in database ‘master’.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
CHECKDB found 0 allocation errors and 0 consistency errors in database ‘mssqlsystemresource’.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Errorlog will show the following entries:
2015-08-25 14:44:22.650 spid63 DBCC CHECKDB (master) executed by sa found 0 errors and repaired 0 errors. Elapsed time: 0 hours 0 minutes 0 seconds. Internal database snapshot has split point LSN = 000000e4:0000017c:0001 and first LSN = 000000e4:00000179:0001.
2015-08-25 14:44:22.840 spid63 DBCC CHECKDB (mssqlsystemresource) executed by sa found 0 errors and repaired 0 errors. Elapsed time: 0 hours 0 minutes 0 seconds.
When the above checks are run, for the master database checks alone the internal transient replica is created. For resource database, no replica is created for the checks.
When the server is started in single_user mode to perform repairs in master, a special mechanism is used. Only for master database, the check command is run with the repair option. When it comes to checking the resource database, the normal check is done with repair disabled. You can see the effect of that from the errorlog entries:
2015-08-25 15:44:22.650 spid61 DBCC CHECKDB (master, repair_allow_data_loss) executed by sa found 0 errors and repaired 0 errors. Elapsed time: 0 hours 0 minutes 0 seconds.
2015-08-25 15:44:22.650 spid61 DBCC CHECKDB (mssqlsystemresource) executed by sa found 0 errors and repaired 0 errors. Elapsed time: 0 hours 0 minutes 0 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, then you will need to replace the resource database if there is any physical corruption introduced in the database. Normally users will not have the ability to write to this database. So unless there is a hardware problem, there is less chance that this database should get corrupted ever.
Though this blog started for an unusual error message, I thought it was important to talk some of these fine prints when working with ResourceDB. Hope you have never encountered any error with ResourceDB, if you have – do let me know via comments to what you did in that situation. Would love to learn from you.
Reference: Pinal Dave (https://blog.sqlauthority.com)
