SQL with Manoj

select * from sys.databases

On executing the above query it gives me 9 records with DBID ranging from 1 to 9. First 4 DBIDs (1-4) for master, tempdb, model & msdb and another 5 databases (5-9) created by me.

But when I queried some DMVs & DMFs it resulted some records with DBID 32767 which left me clueless until I googled about it. Also submitted a post on my favourite MSDN T-SQL forum and got to know that this ID is reserved for Resource Database. The Resource database is readonly database that does not appear on SSMS and is managed internally by SQL Server engine. More on MS BOL about Resources.

To regenerate this scenario, lets do a simple exercise. Execute the following code (make sure you have AdventureWorks sample database installed):

USE [AdventureWorks]
go

-- Execute some sample SQL statements
select top 10 * from Person.Contact
select top 10 * from Production.Product
go 10

-- Execute sp_who2 proc
sp_who2
go 5

Now execute the query below using sys.dm_exec_query_stats DMV & sys.dm_exec_sql_text(sql_handle) DMF to get the status of what SQL engine is doing behind:

select x.dbid, x.text, a.creation_time, a.Last_execution_time, a.execution_count
from sys.dm_exec_query_stats a
cross apply sys.dm_exec_sql_text(a.sql_handle) x
go

Output:-

The results displayed in the image above shows 2 records with DBID 32767.