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)
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 .
[…] Resource Database ID – 32767 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. […]