Could not obtain exclusive lock on database 'model'. Retry the operation later. CREATE DATABASE failed

After installing a SQL server 2008 R2 instance SQL_SERVER\MSSQLSERVER2008R2 side by side on the Box which already has SQL 2005 production instance, we are unable to create a database from the management studio

 

Receiving the below error

 

Create failed for Database 'x'. (Microsoft.SqlServer.Smo)

------------------------------

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)

 

Cause:

By design when we create a database we need exclusive lock on model. When SQL Server creates a new database, "The SQL Server uses a copy of the model database to initialize the database and its metadata". Also, users could create, modify, and drop objects in the Model database. As such, it is important to exclusively lock the model database to prevent copying the data in change from the Model database. Otherwise, there is no guarantee that the content copied from the Model database are consistent and valid.

  • We need to check what is taking the lock on model database

     

  • We ran sp_who2 and found a couple of spids using model with .Net SQLClient Data Provider" as the application

     

  • According to https://blog.sqlauthority.com/2010/01/01/sql-server-fix-error-1807-could-not-obtain-exclusive-lock-on-database-model-retry-the-operation-later/  - we tried to Disconnect and Reconnect your SQL Server Management Studio's session. Your error will go away.
    But this wasn't working for us.

     

     

  • Checked the other basic things
    -SQL startup account was Local System
    -We had permissions on the folder
    -Same file names were not being used
    --They have 6.36TB free in the drive where we create the database.

     

  • We also found that in order to create a database on the SQL 2005 prod instance we need to turn off SQL 2008 instance (Strange!)

 

#---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'

  
-----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')

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

--Now immediately create a database (Before another application takes a lock) 

Resolution

From the sp_who2 output we see that the login XLOGIN\Administrator  is continually querying the model database which in turn is not allowing us to get an exclusive lock on the database                
   
SPID  Status                         Login                                HostName   BlkBy DBName Command          CPUTime DiskIO LastBatch      ProgramName                                    SPID  REQUESTID
----- ------------------------------ ------------------------------------ ---------- ----- ------ ---------------- ------- ------ -------------- ---------------------------------------------- ----- ---------
  
51    sleeping                       XLOGIN\Administrator                   SQL_SERVER   .   master AWAITING COMMAND 0       0      08/06 11:48:37 .Net SqlClient Data Provider                   51    0   
52    sleeping                       XLOGIN\Administrator                   SQL_SERVER   .   tempdb AWAITING COMMAND 0       0      08/06 11:48:37 .Net SqlClient Data Provider                   52    0   
53    sleeping                       XLOGIN\Administrator                   SQL_SERVER   .   msdb   AWAITING COMMAND 0       0      08/06 11:48:37 .Net SqlClient Data Provider                   53    0   
54    sleeping                       XLOGIN\Administrator                   SQL_SERVER   .   tempdb AWAITING COMMAND 16      0      08/06 11:46:25 .Net SqlClient Data Provider                   54    0   
55    sleeping                       XLOGIN\Administrator                   SQL_SERVER   .   model  AWAITING COMMAND 16      0      08/06 11:47:31 .Net SqlClient Data Provider                   55    0   
56    sleeping                       XLOGIN\Administrator                   SQL_SERVER   .   model  AWAITING COMMAND 0       0      08/06 11:46:25 .Net SqlClient Data Provider                   56    0   
57    sleeping                       XLOGIN\Administrator                   SQL_SERVER   .   model  AWAITING COMMAND 0       0      08/06 11:45:19 .Net SqlClient Data Provider 

  • From the input buffer we see the following every time we checked a spid

    Select cat.name as [Name] FROM sys.fulltext_catalogs AS cat ORDER BY [Name] Asc

     

     

  • What was strange was that we did not have Full text Search Service running at all. In fact it isn't installed for SQL 2008R2

There are numerous articles one can find online about this issue, that suggest killing the spid that is taking the lock on the model database. This works for us too, however the solution is temporary.

If I kill the process, in about a minute, it automatically starts up the very same Select statement again ,which was strange!

 

So we dropped and recreated the login XLOGIN\Administrator and the issue hasn't occurred after this. Querying the sp_who2 output doesn't show us the Select statement that was earlier taking the lock.

Hope this simple resolution helps someone who is facing the same issue! J