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

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
  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 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

Comments (0)

Skip to main content