Systems Engineering and RDBMS

Resource Database in SQL Server 2005

Posted by decipherinfosys on January 16, 2008

A new system database was introduced in SQL Server 2005. It is called the Resource database and is a read-only database. It contains all the system objects that ship with SQL Server 2005. So, for example, sys.objects, DMV’s, sp_help etc. physically exists in this database and logically appears in the sys schema of every database on the instance. It does help a lot when doing upgrades or when rolling them back since it is a single place where the changes need to be made. Also, the upgrade is then just a file copy instead of dropping and creating those system objects. So, what does it mean to you and what do you need to be aware of in this regards?

The Resource database files location is always the same as that of the master system database and those are named as:

mssqlsystemresource.mdf : Data File
mssqlsystemresource.ldf : Log File

If for some reason, you need to move the master database, then you would need to move the Resource database as well to that location. Other than this, there will rarely be a need to mess around with this database. At upgrade time, if you run into issues, then you might need to provide the version information to MSFT in order to help troubleshoot the issue. You can use the SERVERPROPERTY() function for that:

SELECT SERVERPROPERTY(‘RESOURCEVERSION’);
GO

and to see when the last update was made to it:

SELECT SERVERPROPERTY(‘RESOURCELASTUPDATEDATETIME’);
GO

One Response to “Resource Database in SQL Server 2005”

  1. […] by decipherinfosys on February 12, 2009 As we had discussed in one of our previous blog post, SQL Server 2005 introduced a new system database called the Resource Database.  In response to […]

Sorry, the comment form is closed at this time.

 
%d bloggers like this: