NULL database name values? Database ID of 32,767? What is going on here?

  • Article

I had a customer inquire as to why they would see results from queries like the following where the database ID value was null in Sql Server 2005 DMV's:
 
  select distinct db_name(database_id) from sys.dm_os_buffer_descriptors b with(nolock);
 
 If you change the query to join to the sys.databases catalog view, you'll 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 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...which I'm sure you realize that you don't have a matching user database for :-).
 
 What is going on here????  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 that it isn't 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). So, in this case, simply modify the first query to be something like this:
 
  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

Chad Boyd ~~~ This posting is provided "AS IS" with no warranties, and confers no rights. Use of any included script samples are subject to the terms specified at https://www.microsoft.com/info/cpyright.htm.