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.The Situation:
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).
Solution:
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.
SQL Journey 
Prashant said
In stead of the query in Step:2, you can use the below query:
select l.resource_type, d.name, l.request_mode, l.request_type, l.request_status
from sys.databases d inner join sys.dm_tran_locks l
on d.database_id = l.resource_database_id
Amit Mistry said
What is the permanent solution of this problem? I did fresh installation and trying to create from scratch adding new testDB or attaching the sampleDB having the same problem everytime.
Why we need to touch the default setting … ?
Appreciate your guidline
Prashant said
Amit,
This happens when model database is used by some other process while you creating a new database OR model database is down that time.
In your case it seems model database is down when your are creating new databases. So you can check if the AutoClose property of model is ON and if so just turn it OFF.
Here is the query:
SELECT DATABASEPROPERTYEX(‘model’, ‘IsAutoClose’) AS [AutoClose]
GO
ALTER DATABASE [model]
SET AUTO_CLOSE OFF
GO
Otherwise check if model is being used by anyother process and do the next step accordingly.
SELECT
p.spid, p.hostname, p.program_name, p.loginame, p.login_time, p.status
FROM master..sysprocesses p
WHERE
DB_NAME(dbid) = ‘model’
GO
Thanks,
Prashant
Fix Graphite Database Error Database Is Locked Windows XP, Vista, 7, 8 [Solved] said
[…] Fixing Error Message 1807 could not obtain exclusive lock … – Jun 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 …… […]