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)

Solarwinds

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)

Solarwinds
Previous Post
SQL SERVER – Common Table Expression (CTE) and Few Observation
Next Post
SQL SERVER – expressor 3.2 Release Review

Related Posts

17 Comments. Leave new

  • 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

    Reply
    • 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

      Reply
  • hi pinal sir,
    me too wud like to know answer of virul’s question :-)

    Reply
  • Hey Pinal,

    32767 is looking a major number interms with sql server.Max user connection is also 32767.

    Select @@MAX_CONNECTIONS ’ you will get result 32767.

    -Anup

    Reply
  • Nakul Vachhrajani
    May 10, 2011 10:23 pm

    Interesting how in the days of 64-bit systems, we still have 8K pages, and 32767 as the ID of the resource database…..very interesting.

    Reply
  • 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…!

    Reply
  • 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.

    Reply
  • Write check Constraint for it .

    Reply
  • hi Pianal, please tell me why reource databse is hidden in sql server

    Reply
  • Hi Pinal,

    Thanks for this post. I have a follow-up question. I’m having a deadlock issue where the Database Id = 32767. I have the Object Id but how do I find which object in the Resource Database it is. The following query returns no matches for the object_id when ran against each db.

    SELECT name, object_id, principal_id, schema_id, parent_object_id,
    type, type_desc, create_date, modify_date,
    is_ms_shipped, is_published, is_schema_published
    FROM sys.objects

    Any thoughts would be greatly appreciated.

    X

    Reply
    • can you share deadlock graph?

      Reply
      • Xarles Schwarz
        July 14, 2015 12:22 am

        Please see attached files.

      • attached? where?

      • Please see the xml below.

        xp_userlock

        exec @result = sys.xp_userlock 0, @dbid, @DbPrincipal, @Resource, @mode, @owner, @LockTimeout

        Proc [Database Id = 32767 Object Id = 155622469]

        xp_userlock

        exec @result = sys.xp_userlock 0, @dbid, @DbPrincipal, @Resource, @mode, @owner, @LockTimeout

        Proc [Database Id = 32767 Object Id = 155622469]

  • Sam – It looks like you have requirement of your own locking mechanism. I would suggest to look at long running transaction and check why locks are not released.

    Reply
  • Hi Pinal,

    We have maintenance job configured . But everytime it fails because of deadlock.Please find the below deadlock graph for the same:

    Wait-for graph
    NULL
    Node:1

    OBJECT: 5:261575970:8 CleanCnt:3 Mode:IS Flags: 0x1
    Wait List:
    Owner:0x00000001EB6C71C0 Mode: Sch-M Flg:0x42 Ref:1 Life:20000000 SPID:61 ECID:0 XactLockInfo: 0x000000329D65B6E8
    SPID: 61 ECID: 0 Statement Type: EXECUTE Line #: 1
    Input Buf: RPC Event: Proc [Database Id = 32767 Object Id = -710746692]
    Requested by:
    ResType:LockOwner Stype:’OR’Xdes:0x00000032298516A8 Mode: Sch-S SPID:58 BatchID:0 ECID:0 TaskProxy:(0x00000045772E25D8) Value:0xf5b14c0 Cost:(10/0)
    NULL
    Node:2

    OBJECT: 5:261575970:8 CleanCnt:3 Mode:IS Flags: 0x1
    Grant List 2:
    Owner:0x00000003E04D63C0 Mode: IS Flg:0x40 Ref:2 Life:02000000 SPID:58 ECID:0 XactLockInfo: 0x000000051D0B2D68
    SPID: 58 ECID: 0 Statement Type: ALTER INDEX Line #: 1
    Input Buf: Language Event: DECLARE @Server_Name Varchar(max);

    SET @Server_Name=(select RCS.replica_server_name FROM [JLIGROWCORPPRD].[master].sys.availability_groups_cluster AS AGC
    INNER JOIN [JLIGROWCORPPRD].[master].sys.dm_hadr_availability_replica_cluster_states AS RCS

    Requested by:
    ResType:LockOwner Stype:’OR’Xdes:0x000000329D65B6A8 Mode: Sch-M SPID:61 BatchID:0 ECID:0 TaskProxy:(0x00000047362BA608)
    ………….

    Could you please suggest what can be done to avoid this situation.

    Thank you !!!

    Reply

Leave a Reply

Menu