SQL Server Resource Database Values in DMVs


By:   |   Updated: 2007-02-16   |   Comments   |   Related: More > Dynamic Management Views and Functions

Problem
Multiple queries against system dynamic management views and catalog views result in database ID's and names with a NULL value. Is something wrong with my queries?  How can the database name or ID be NULL?  Can I modify my queries to capture the correct values?

Solution
The answer is due to the new system database introduced in SQL Server 2005 called the
RESOURCE database. This database will always show a database_id value of 32767 when exposed within system DMVs or Catalog Views.  Note - The Resource database is not exposed in all DMVs or Catalog Views, as evident by a simple query against the sys.databases Catalog View, where you won't see an entry for it.  This is often the cause for retrieving NULL database name or database id values when joining to other system views/functions. One such example is as follows:

select distinct db_name(database_id)
from sys.dm_os_buffer_descriptors b with(nolock);

If you change this query to join to the sys.databases catalog view, you will notice that the NULL database name values disappear, however so do the resulting records from the sys.dm_os_buffer_descriptors where the prior statement was returning a NULL db_name() value:

select distinct d.name
from sys.dm_os_buffer_descriptors b with(nolock)
join sys.databases d with(nolock)
on b.database_id = d.database_id

If you run the first query without using the db_name() function, you'll notice that the database_id values for some of the entries in the sys.dm_os_buffer_descriptors DMV are the value of 32767 - the ID value for the RESOURCE database.

A simple tweak to the query will provide you with the appropriate results:

select distinct case when database_id = 32767 then 'resourceDb' else db_name(database_id) end
from sys.dm_os_buffer_descriptors b with(nolock);

Or the 2nd query to something like this:

select distinct case when b.database_id = 32767 then 'resourceDb' else d.name end
from sys.dm_os_buffer_descriptors b with(nolock)
left join sys.databases d with(nolock)
on b.database_id = d.database_id

Next Steps



Last Updated: 2007-02-16


get scripts

next tip button



About the author
MSSQLTips author Chad Boyd Chad Boyd is an Architect, Administrator and Developer with technologies such as SQL Server, .NET, and Windows Server.

View all my tips




More SQL Server Solutions











Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.






download


get free sql tips

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



Learn more about SQL Server tools