Recognizing the Resource Database

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.

Starting Lineup

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 Glue

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!