SQL Server 2005 Resource Database Values in Dynamic Management Views\Functions
Written By: Chad Boyd -- 2/16/2007
-- read/post comments
-- print --
Rating:
(not rated yet)
Rate
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
- Check your existing scripts that access the DMVs to validate they are returning correct data. If not, consider some of the techniques in the scripts above to ensure all of the databases are represented properly.
- With the DMV's comes a whole new set of opportunities to gain valuable insight into the SQL Server engine. These views and functions should not be overlooked when troubleshooting an issue.
- If you have not had an opportunity to experiment with the DMV's, check out the following resources:
- Special thanks to Chad Boyd from the MSSQLTips.com community for this tip.
Readers Who Read This Tip Also Read
Free Live Webcast
Comment or Ask Questions About This Tip
|
