3 Things to Remember about SQL Server mssqlsystemresource Database

If you are a SQL Server DBA, you must have heard of database called mssqlsystemresource. This mystery database isn’t known by many because this is not visible to all. There are few special characteristics of this database which are often misunderstood, but good to know about. The reasons for this database will become evident after you read this article.

First, let’s understand the basics about this database:

• This is a hidden database which can be seen only via DAC connection when SQL is running in single user mode.
• This database contains only the definition of system objects.
• No data specific to instance is stored in this database.
• You can treat this as one of the binary file (like DLL or EXE).
• We believe that it is called as database because it has two files (MDF and LDF) and internally queried by SQL Server.
• The database ID for this database is always 32767.

Here are few important considerations you should know about this database.

1. Born in SQL Server 2005: This database was introduced in SQL Server 2005. Prior to SQL 2005, a sysadmin had permission to modify data in system tables (like sysdatabases, sysobjects, sysindexes etc.) which might lead to instability of whole SQL Server instance, if not modified correctly. In SQL Server 2005, the Microsoft SQL Server product team moved all definitions to this read-only database and all system tables have changed to catalog views. Modification into the system table was mostly done to recover from the database corruption (database into suspect mode). If you have worked with earlier versions of SQL Server (before 2005), then you might recall that the only way to bring the database into emergency mode was to modify the status bit in sysdatabases system table. After SQL Server 2005, Microsoft has extended the ALTER DATABASE command to bring the database into emergency mode and there is no need to update the system table manually. In short, sysadmins will not be able to modify data in system tables, which makes SQL Server more stable.

2. Famous Myth – It can be used for Service Pack Rollback: You might find information in some places on the Internet that the mssqlsystemresource database can be used to rollback a service pack applied to SQL Server. This is a myth. Applying a service pack is not just changing the MDF and LDF files of the database. Replacing the files of this hidden database is just one part of whole service pack / hotfix installation process. In earlier versions of SQL Server, whenever a service pack or a patch has to be applied and system tables needs modification, it was done using alter command. In SQL Server 2005, Microsoft has made a smart move by moving all system level objects (table procedure and function) definition into this database. Due to this change, there is no need to alter the system objects, but instead just replace the MDF and LDF file of this new database. As per Microsoft documentation, it has helped them in reducing patching time.

3. Maintenance of resource database – Some DBAs think that because this is a database, it needs maintenance like a regular database, which is not completely true. As stated earlier, this database should be treated as any other binaries available with SQL Server. In some versions of SQL Server, the MDF and LDF files of the database were kept in the data folder; this has caused confusion about how to take a backup of the MDF and LDF files. In current versions of SQL Server, the files are kept in binn folder. This is the same folder that contains the major executable of SQL Server, that is the sqlservr.exe file. Since this is a read-only database and there is no data modification, there is no need to do the rebuild indexes task for this database. There is no way to run an explicit checkDB in this database, but whenever a checkDB is performed on the master, it automatically performs a checkDB into this database as well. Here is the snippet of DBCC CHECKDB(‘master’) command which shows checkDB for hidden database also.

DBCC results for 'master'.

..

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.

If there is any corruption found in the database, the database file can be picked from any other SQL Instance having strictly same version of SQL Server and replaced.

Hopefully, this article has cleared up a few myths about this hidden database.