SQL SERVER – Resource Database ID – 32767

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 (http://blog.SQLAuthority.com)

11 thoughts on “SQL SERVER – Resource Database ID – 32767

  1. Hi Pinal,

    I have a question, I am working as Jr. DBA position having this questions.

    I dont know, where to ask my question in your blog.

    1. How to recover full database (Point in time), while we are not able to take transaction log tail backup.

    2. How to recover full database (Point in time), while full database backup (Main backup) is currepted.

    Thanks, Virul

    Like

    • Virul,

      If you need to have Point in time recovery,then tran_log backup needs to be run.The interval of tran_log backup depends on how much critical your environment is.

      Differential backup will ensure that the number of tran_log which needs to be restored during a failure is reduced.Differential backup will have all the changes made to the database after a last full backup.

      Note : Taking backup doesnt mean that your disaster management is in place.The backup file should be valid for a restore and this is most important.

      -Anup

      Like

  2. Hello Pinal,

    I am creating one SP & use ‘with Encryption’ in it ,Now it becomes Encrypted(Lock appear on it).
    How can I see it again…(without third party tool)?
    ‘sp_HelpText’ Not Work…

    Please Help…!

    Like

  3. sir can u plz tell me how to write a query to
    insert the record into the database by date/time .The same id will not inserted another time until a month over..
    For example if i m giving salary to the employee then if i m trying to insert sal within in a month 2 times it should not work.

    Like

  4. Pingback: SQL SERVER – Weekly Series – Memory Lane – #028 | SQL Server Journey with SQL Authority

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