Can you name each of the system databases in SQL Server?
You might be able to name master, msdb, model, and tempdb. What about the fifth system database? Don’t feel bad if you’re drawing a blank.
The fifth system database is the Resource database. Even if you’ve never heard of the Resource database, chances are that you’ve used it. Let’s discuss how that can be.
Let’s review what each of the commonly known system databases are used for:
master – contains server configuration settings, database file information
msdb – contains information related to jobs and alerts such as scheduling information and job history
model – template database used for any new databases that are created
tempdb – location to store temp tables and other temp objects
In addition to these four, there is the Resource database. The Resource database contains the system objects for each database. For example, if you search sys.objects on two different databases, you’ll see a result unique to each database. Both of those results are actually stored in the Resource database.
In the Shadows
Fear not if you search for the Resource database in SSMS and can’t find it. The Resource database isn’t shown alongside the rest of the system databases mentioned above. You can still find evidence of it’s existence in other ways.
Did you know SQL Server can have a maximum of 32767 databases per instance? You can answer two trivia questions at once if you remember that the maximum number of databases per instance also matches the database ID (32767) of the Resource database.
The Resource database is well hidden, it’s not completely hidden. With 32767 in mind, if I run this script querying cached plans:
SELECT dbid, objtype, text FROM sys.dm_exec_cached_plans CROSS APPLY sys.dm_exec_sql_text(plan_handle); GO
I see a couple rows with a database ID of 32767. This is referencing the Resource database.
The Resource database is the perfect team player. It stays out of the spotlight and does well to support the rest of the databases on the server. If you’ve ever queried the sys schema of a database, you have the Resource database to thank.
Thanks for reading!
2 thoughts on “Recognizing the Resource Database”
You can also see the resource database by doing
After the check of master it does a check of ‘mssqlsystemresource