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)
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
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
hi pinal sir,
me too wud like to know answer of virul’s question :-)
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
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.
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…!
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.
You should check if there is an entry in the table for the current month
Write check Constraint for it .
hi Pianal, please tell me why reource databse is hidden in sql server
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
can you share deadlock graph?
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.
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 !!!