Systems Engineering and RDBMS

Archive for January 16th, 2008

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

Posted in SQL Server | 1 Comment »