Earlier I blogged about SQL SERVER – What Kind of Lock WITH (NOLOCK) Hint Takes on Object?. After reading the post, I got question by one of the blog reader.
“Hi Pinal,
I see in your blog post you have Database ID which is 32767. Everytime I want to get the name of the database from database_ID I use following function but this time this function returned NULL.
SELECT DB_NAME(32767)
When I tried to list all the databases uses following script it did not have that database ID as well.
SELECT *
FROM sys.databases
I assume you have created this many database is that true?”
Very interesting question to me. I have never thought about it when I posted as I assumed few things. Here is the answer. Database_ID 32767 is reserved Resource Database.I have not created that many databases. This database is hidden from users in SSMS but you can see that if you go to file folder. You can read more about the same over here SQL SERVER – Location of Resource Database in SQL Server Editions.
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. (from BOL).
In SQL Server maximum databases per instances can be created are 32,767. This last number has been reserved by Resource Database itself.
Reference : Pinal Dave (https://blog.sqlauthority.com)
