Fixing Error Message 1807 could not obtain exclusive lock on database ‘model’ in SQL Server
Posted by Prashant on June 15, 2010
Some days back I got this question from one of my friend, when he was trying to create a New Database, he is getting the below error message.Msg 1807, Level 16, State 3, Line 1 Could not obtain exclusive lock on database ‘model’. Retry the operation later. Msg 1802, Level 16, State 4, Line 1 CREATE DATABASE failed. Some file names listed could not be created. Check related errors.
He was trying to create table on Model database first then he tried to create a New Database. As Model database is used as a template for a database being created, SQL Server tries to obtain an Exclusive Lock on Model database. So if one session is using model database SQL Server can’t obtain exclusive lock on model.
If you are using SQL Server Management Studio try these steps:
Step1: Open a New Query (say SQL Query1.sql) and select “model” from database dropdown box.
Step2: Now in another Query (say SQL Query2.sql) run the below query:
select * from sys.dm_tran_locks
Observation: You will find result like below.(Click on the image to enlarge)
This indicates Shared Lock has been Granted on resource_database_id = 3 (database_id: 3 is for model)
Step3: Now try to create database either from Object Explorer of SQL Server Management Studio or by CREATE DATABASE statement.
Step4: This time run query in step-3 and observe the result.(Click on the image to enlarge)
This indicates an Exclusive lock on model database but has not been granted yet (it’s in WAIT state).
Make sure to disconnect all the sessions which uses model database then CREATE DATABASE statement later. If this problem still remains, restart management studio and try again.
This is tested in SQL Server 2008, also leave your suggestions.