Thursday, 24 October 2013

Error: 1807 Could not obtain exclusive lock on database ‘model’. Retry the operation later

Error:

TITLE: Microsoft SQL Server Management Studio

------------------------------
Create failed for Database 'Test'.  (Microsoft.SqlServer.Smo)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1600.1+((KJ_RTM).100402-1539+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Create+Database&LinkId=20476
------------------------------
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
Could not obtain exclusive lock on database 'model'. Retry the operation later.
CREATE DATABASE failed. Some file names listed could not be created. Check related errors. (Microsoft SQL Server, Error: 1807)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1600&EvtSrc=MSSQLServer&EvtID=1807&LinkId=20476
------------------------------
BUTTONS:
OK
------------------------------





Solution:

Solution 1:

            Disconnect and Reconnect your SQL Server Management Studio’s session. Your error will go away.

Solution 2:


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: 52)

We have to identity what kind of query/activity being performed by this session(52).

DBCC InputBuffer(52) 


    EventInfo column returns the query performed, Based on that, you have to decide whether the Session ID(52) can be Aborted/ Not. If you want to abort the session(52),
run the command 
    Kill 53 

    Now you can try to create a new Database..!