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..!  

2 comments:

  1. Looking for cryptocurrency article please visit us:
    Mengenal Cryptocurrency
    Kompetisi Blog
    Cara Investasi Aset Crypto
    Cara Klaim Hadiah
    Token Pintu
    Aplikasi Trading Crypto
    Cryptocurrency is a type of digital currency that generally only exists electronically. There is no physical coin or bill unless you use a service

    ReplyDelete
  2. The Best Rules for Baccarat | Wilbur's
    In this game, you pick five different colored 온카지노 cards from 메리트 카지노 고객센터 an original deck of 52. The game starts with a high probability of winning. In this 바카라

    ReplyDelete