SQL Server : When creating a new database...

Posted by PandianS under Error and Solution on 12/5/2010 | Points: 10 | Views : 11981 | Status : [Member] [MVP] | Replies : 2

Many of us may experience this error when creating a new Database..
Msg 1807, Level 16, State 3, Line 1
Could not obtain exclusive lock on database 'model'. Retry the operation later.
Root Cause: Root cause of this error may be one of the following reason
1. Somebody exclusively open the Model database in different session
2. If more than one create database statements attempt to run simultaneously

How to identity:
Use master 
GO
IF EXISTS(SELECT request_session_id FROM sys.dm_tran_locks
WHERE resource_database_id = DB_ID('Model'))
PRINT 'Model Database being used by some other session'
ELSE
PRINT 'Model Database not used by other session'
So we can identify that whether the Model database being execlusively used by any session.., If you found anybody using the Model database execlusively, You can identify what kind of statement being used by the session...using the script given below
SELECT request_session_id  FROM sys.dm_tran_locks
WHERE resource_database_id = DB_ID('Model')
The script will return the session ID (i.e: 53)

We have to identity what kind of query/activity being performed by this session(53).
DBCC InputBuffer(53)
EventInfo column returns the query performed, Based on that, you have to decide whether the Session ID(53) can be Aborted/ Not. If you want to abort the session(53), run the command
Kill 53
Now you can try to create a new Database..!

Cheers
www.SQLServerbuddy.blogspot.com
iLink Multitech Solutions



Responses

Posted by: Vuyiswamb on: 12/5/2010 [Member] [MVP] [Administrator] NotApplicable | Points: 25

Up
0
Down
Nice, thanks for sharing

Thank you for posting at Dotnetfunda
[Administrator]

PandianS, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Ravidnrabarange on: 7/15/2016 [Member] Starter | Points: 25

Up
0
Down
Thanks it worked for me...

?@V!ndra

PandianS, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response