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.
Chad is an Architect, Administrator, and Developer with technologies such as Sql Server (and related technologies), .NET, Windows Server, and the Microsoft Clustering stack (MSCS, NLB, HPC). After nearly 5 years at Microsoft in a variety of roles related to pushing the limits of the Microsoft stack in many of the largest, most complex Sql Server installations in the world, he now provides consulting to many different customers and clients ranging from startup to Fortune 50. Most recently he has been spending much of his time as an architect at SpruceMedia, Inc., a startup firm in the Facebook/Social Advertising space (http://sprucemedia.com) and resides in the Redmond, WA area where he also spends a significant amount of time writing, talking, presenting and blogging about the data-related technologies on the Microsoft stack. Chad regularly posts Sql Server related content, tools, and advice with the MSSQLTips team and on stackoverflow.com at http://stackoverflow.com/users/169012/chadhoc. Chad can be contacted via email at chad dot boyd dot tips at gmail dot com.