SQL Server Resource Database Values in DMVs

By:   |   Comments   |   Related: > 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


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



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.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips



Comments For This Article

















get free sql tips
agree to terms