THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Kalen Delaney

Geek City: The Resource Database

Long shrouded in mystery, this nebulous hidden database has long been a source of burning curiosity to SQL Server administrators and developers alike.  Like a mirage in the deep desert, as soon as you get close, it is no longer there. Like a shimmer seen from the corner of your eye, as soon as you look at it straight on, it disappears.

When the very early betas of SQL Server 2005 came out, and we were told that there was a new invisible system database, true SQL Server geeks were not content to just accept that at face value. We had to find a way to see what Microsoft did not want us to see. And a way was found...

Although in most situations, SQL Server does not show you this database, it does leave evidence that even a mediocre tracker can find. If you look in your SQL Server's default data directory (by default, C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data) you'll see the files for this database, which are not actually named 'Resource', but rather something quite a bit longer. The files are mssqlsystemresource.mdf and mssqlsystemresource.ldf. These files are quite necessary to your SQL Server's operations. If you try to move, drop or rename these files while your SQL Server is running, you will get a message that the files are in use. If you stop your SQL Server and move, drop or rename one of these files, your SQL Server will not start.

These files support a database called mssqlsystemresource, which, as alluded to in the title of this post, is referred to as the Resource database. This is the database where the definitions of all programmable system objects are stored, such as your system stored procedures, system functions and all the definitions of the new metadata views. This includes the compatibility views like sysdatabases and sysobjects, as well as the catalog view like sys.databases and sys.partitions.  For all practical purposes, this database is 'read only'. During upgrades, a new Resource database will replace the old, with enhancements to existing object definitions along with new object definitions. And because you can see the definitions of any objects using the  sp_helptext procedure or the object_definition function, there should be no need to actually access this database.

But because it's there, we must. In early betas, geeks of my acquaintance quickly discovered that you could stop your SQL Server and copy the mssqlsystemresource.mdf and mssqlsystemresource.ldf files. Your SQL Server can then be restarted normally, and then you can attach the two copied files under a new database name that becomes as accessible as any other user database. Of course, any change to the objects in this cloned database would have absolutely no impact on the real object definitions stored in the real Resource database.

A second method of access was determined closer to the time of SQL Server  2005's release. If you start your SQL Server in single user mode, the Resource database is available. This doesn't mean changing a database property to 'single user', it means stopping your SQL Server, and restarting with the -m flag.

I mentioned this little tidbit to the students in the SQL Server internals class that I was teaching in Melbourne a few weeks ago, and one of the students sent me a followup email indicating that he tried to do this. He started his SQL Server with -m, but could not 'see' the hidden database.  So I decided to do some further investigation.

It turns out that even in single user mode, you still cannot see this hidden database. If you select from sys.databases, you will not see the database. But you can still USE the database:

USE mssqlsystemresource

You can verify you are in the Resource database, by running the following:

SELECT db_name()

It turns out that the definition of the sys.databases view has a specific exclusion for the Resource database. It joins several real system tables together, and then includes this filter:

WHERE d.id < 0x7fff

The value 0x7fff is 32767 in decimal, which is the id of the Resource database. You can see this for yourself by looking at the real system table. Inspecting the definition of sys.databases further, you can see that the 'd' prefix corresponds to the table sys.sysdbreg. As a real system table, you can only see the data in this table when you have connected to SQL Server using the Dedicated Administrator Connection (DAC).  Once you have done that, you can see the names and id's of all databases:

SELECT id, name FROM master.sys.sysdbreg

Here's a subset of the output I get when I run the above query:

 

1        master
2        tempdb
3        model
4        msdb
5        AdventureWorks
6        pubs
7        Northwind
8        credit
16       Performance
32767    mssqlsystemresource

So you can now poke around all you want, but you'll probably find it rather boring. There really is nothing in the Resource database that you can't see using documented interfaces, but some people won't believe it until they see for themselves.

 

Have fun!

~Kalen

Published Thursday, September 13, 2007 3:26 PM by Kalen Delaney

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

James Luetkehoelter said:

Very nice Kalen - I have people ask me all the time (once I explain what the resource database is) "How do I look at it". My general answer it "you can't" meaning "I don't want you to :) ". This is definitely nice for the geekier of us to explorer, but you're right, there isn't much new to learn...except when a new service pack or hotfix comes out...hmmm...

I especially love your writing - beautiful opening paragraph. Did that take awhile or did it flow out naturally?

September 16, 2007 9:33 AM
 

Lara Rubbelke said:

Nicely done again!  As for the opening paragraph - I expect Kalen is taking some evening classes in creative writing so she can one day pursue her dream of writing sci-fi novels.  

September 16, 2007 5:02 PM
 

Kalen Delaney said:

Nope, no writing of sci-fi novels for me. If I did that, I'd have less time to READ sci-fi and watch Star Trek reruns.

September 27, 2007 4:45 PM
 

lyda said:

can u update the system table like syscolumns using the above describe connection?

October 15, 2007 1:57 AM
 

yup said:

Here is what I did

>>Started SQL 2005 in single user mode.

--net start mssql$s9c -m

>>Connected as DAC

--sqlcmd -E -S .\s9c -A

>>Check all the databases

--Select * from sys.sysdbreg

While I can see dbid 32767 or mssqlsystemresource in the output rows an attemt to get inside failed. This was the sql statement used and the output

>>use mssqlsystemresource fails

--Msg 911, Level 16, State 1, Server WIN-RCMI11NRUK0\S9C, Line 1

--Could not locate entry in sysdatabases for database 'mssqlsystemresource'. No entry found with that

--name. Make sure that the name is entered correctly.

Any idea if can avoid going through sysdatabases for this ?

Its on sql DEV 2005 - 9.00.5057 (X64) Mar 25 2011 13:33:31 on Windows NT 6.1 (Build 7601: Service Pack 1)

September 5, 2011 2:07 AM
 

yup said:

Sorry for the un checked approach - did this again by starting SQL Server as a program & its working.

D:\Program Files\Microsoft SQL Server\MSSQL.5\MSSQL\Binn\sqlservr.exe -sS9C -m

September 5, 2011 2:18 AM
 

Kalen Delaney said:

Hi Yup

Yes, you figured it out. /m is a parameter to the sqlservr.exe program, not to net start.

~Kalen

September 5, 2011 12:42 PM
 

yup said:

:) sorry for the trouble as learnt this works with service or program provided one selects the right parameters - or /.

Some thing strange that showed up on my instance is this

<<doesnt matters how I start my sql server (with parameters m / f) sqlservr.exe or net>>

select the sys files for resource DB by

use mssqlsystemresource; select * from sysfiles

File data & log are listed at

data E:\sql9_sp2_t\sql\ntdbms\scripts\usa\x86\mssqlsystemresource.mdf

log E:\sql9_sp2_t\sql\ntdbms\scripts\usa\x86\mssqlsystemresource.ldf

While doing a select * from master.sys.sysdbfiles shows the correct one.

data E:\DB\MSSQL.5\MSSQL\DATA\mssqlsystemresource.mdf

log E:\DB\MSSQL.5\MSSQL\DATA\mssqlsystemresource.ldf

Any idea why? I've seen this happening for user databases where sysfiles from userDB has different but correct info compared to sysaltfiles (which is the default table to pull DB properties & backups file mapping) but in this case its the internal DB table thats in correct and it belongs to a system DB.

Some where you can guide me ?

October 10, 2011 4:18 AM
 

yup said:

These path change in SQL versions and after applying a service pack. As suggested they seem to be from the station used to build this db.

October 15, 2011 3:31 PM
 

SQLServerGeeks.com – SQL Server: Resource Database said:

June 24, 2014 3:00 AM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Favorite Non-technical Sites or Blogs

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement