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
Comments
Anonymous
December 28, 2015
This hit us too. The user was a local system acct though. Put model in RESTRICTED_USER mode, then app was able to create a new DB....which was in RESTRICTED_USER mode. Put both DBs back into MULTI_USER mode.Anonymous
May 18, 2016
Thank you !Anonymous
February 15, 2017
Fantastic!! Thank you for sharing.Anonymous
April 21, 2017
Awesome things here. I am very glad to peer your article.Thank you so much and I am looking forward to contact you.Will you please drop me a e-mail?Anonymous
July 15, 2018
Thank you very much!