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






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


Article Last Updated: 2007-02-16

Comments For This Article





download














get free sql tips
agree to terms