Tuesday, March 4, 2014

Database_id Number 32767 in MS SQL SERVER--- Resource Database

This Morning While I was executing the DMV's to view the stats of procedure in MS SQL Server I got one very interesting thing which I just want to share with you guys....When I executed the DMV as "select * from sys.dm_exec_procedure_stats" and I started looking at database_id. Thing that caught my attention was database_id 32767. First I thought that I have never been created this amount of databases so it must be system database but which one, as master,tempdb,model,msdb,report server database_id's are in(1,2,3,4,5). The next thing which came to my mind is that I can get the database name by using  select db_name(32767). I did so but was not able to see the name as it was giving NULL as result. I was amazed how it is possible..Then I search it on google and the answer to this question was "Resource databaseWhich is hidden from users in SSMS. I was aware of this database but never thought about the database_id number. 

As we know In SQL Server we can not create more than 32767 databases. So database_id 32767 is the last database_id which is reserved for Resource database

The Resource database is a read-only database that contains all the system objects that are included with SQL Server.SQL Server system objects, such as sys.objects, are physically persisted in the Resource database, but they logically appear in the sys schema of every database. The Resource database does not contain user data or user metadata. 

The physical file names of the Resource database are mssqlsystemresource.mdf and mssqlsystemresource.ldf. These files are located in <drive>:\Program Files\Microsoft SQL Server\MSSQL11 <instance_name>\MSSQL\Binn\. Each instance of SQL Server has one and only one associated mssqlsystemresource.mdf file, and instances do not share this file.

Thanks!! 

No comments:

Post a Comment