Advertisements
Home > DB Concepts, DBA Stuff > SQL DBA – DBID 32767 | Resource Database

SQL DBA – DBID 32767 | Resource Database


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

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: