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?


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.

AS [Resource Version];
SELECT SERVERPROPERTY('ResourceLastUpdateDateTime')
AS [Resource Last Update DateTime];
AS [SQL Definitions];

SQL SERVER - mssqlsystemresource - Resource Database ResourceDatabase

Reference : Pinal Dave (https://blog.sqlauthority.com)

SQL Backup and Restore, SQL Scripts, SQL System Table
Previous Post
SQLAuthority News – Readers Selection – Readers Most Favorite Articles
Next Post
SQL SERVER – Simple Example of Recursive CTE

Related Posts

11 Comments. Leave new

  • 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.

  • Slaven Sola
    April 6, 2010 6:21 pm

    I think that method is the same like before.
    Only difference is that now resource database is in binn folder.

  • Slaven Sola
    April 6, 2010 9:07 pm

    Sorry, it is not the same :)
    I will try something and post here result.

  • dear Pinal,

    there is any way or situation when we see the resource database in system databases node.


  • Does Resource database grow?

    • 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.


  • Which date this
    SELECT SERVERPROPERTY(‘ResourceLastUpdateDateTime’)

    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.

  • I could not find the ‘mssqlsystemresource’ I am on SQL 2008 ENT

  • 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…

  • Akhilesh Narayanan - CBD - DUBAI
    July 16, 2013 9:33 pm

    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?

  • 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.


Leave a ReplyCancel reply

Exit mobile version