SQL SERVER – mssqlsystemresource – Resource Database

Just a day ago I received following email

“Dear Pinal,

While I was exploring my computer in directory C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data I have found database mssqlsystemresource. What is mssqlsystemresource?

Thanks,

Joseph Kazeka”

Simple question like this are very interesting. mssqlsystemresource is Resource Database. It is read only database and contains system objects (i.e. sys.objects, sys.modules and other sys schema objects). Resource database does not contain any of user data.

The purpose of resource database is to facilitates upgrading to new version of SQL Server without any hassle. In previous versions whenever version of SQL Server was upgraded all the previous version system objects needs to be dropped and new version system objects to be created. With additional of resource database when SQL Server is to be upgraded to new version resource database has to be simple replaced as file system level (using OS). If new version is creating problems rolling back can be done very easily, just replace resource database with previous version.

For each SQL instance there is only one resource file. Resource file location should be same as master database. This databse is hidden from the user. It can not be viewed in Object Explorer or with the use of sp_helpDb or any other way. SQL Server itself can not backup this database. This database has to be backed up using filesystem backup.

Another interesting fact about Resource Database is its fixed ID 32767. Run following T-SQL script to see some of the properties of the Resource Database.

SELECT SERVERPROPERTY('ResourceVersion')
AS [Resource Version];
GO
SELECT SERVERPROPERTY('ResourceLastUpdateDateTime')
AS [Resource Last Update DateTime];
GO
SELECT OBJECT_DEFINITION(OBJECT_ID('sys.objects'))
AS [SQL Definitions];
GO

Reference : Pinal Dave (http://blog.SQLAuthority.com)

About these ads

13 thoughts on “SQL SERVER – mssqlsystemresource – Resource Database

  1. Do you known a method of object tampering within SQL Server 2008?

    The goal is to use an attached copy of mssqlsystemresource db and alter some system stored procedures, and then replace this database with the original one. This operation was possible in SQL 2005… and saved me many hours of work.

    Like

    • resource database does not grow. with each upgrade the size will increase. but on regular basis it won’t increase because it does not hold any data. it holds only medata of objects under sys schema.

      Thanks,
      Brahma

      Like

  2. Pingback: SQL SERVER – Importance of Resource Database – Quiz – Puzzle – 18 of 31 « SQL Server Journey with SQL Authority

  3. Which date this
    SELECT SERVERPROPERTY(‘ResourceLastUpdateDateTime’)
    provide?

    Yesterday(7th Apr 2012) i patched a server with SP4 CU3(SQL server 2005) and when i checked using the above command i got a datetime of Dec 2011.

    I am confused abt this datetime, please clarify.

    Like

  4. How much rows in sys.conversation_endpoints? Simple “SELECT COUNT(*) FROM sys.conversation_endpoints nolock” takes more than 10 minutes. We have ~50.000.000 row, so I look the query from metatables. I know that the view sys.conversation_endpoints$ built from sys.sysdesend join sys.sysdercv tables. Amazing, but both have RecordCount = 0. I don’t need exact number of rows, just estimation…

    Like

  5. Dear Pinal, I would like to know the following about mssqlresource database. Is this contains service pack upgrade? Can we use the upgraded resource database to another instance and save time of service pack installation?

    Like

  6. Pingback: SQL SERVER – Weekly Series – Memory Lane – #039 | Journey to SQL Authority with Pinal Dave

  7. Hi Pinal,
    I see that the workload on the resource database is high. Is there a way to optimize this?Thank you so much. Your blog is really helpful and very informative.

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s