SQL Journey

Journey of SQL Server & Microsoft Business Intelligence

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.

4 Responses to “Fixing Error Message 1807 could not obtain exclusive lock on database ‘model’ in SQL Server”

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

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

  3. […] 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 …… […]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: