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 (https://blog.sqlauthority.com)
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.
I think that method is the same like before.
Only difference is that now resource database is in binn folder.
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.
Thanks
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.
Thanks,
Brahma
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.
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…
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.