SQL Server Exclusive Lock on Database ‘model’

model database is one of the sytem databases that act as a template database for creating new database, also the size of new database should be greater or equal to model database. We can create, modify and drop objects in the Model database. The newly created database will have all the DB objects including data in tables that were there in “model” database at the time of DB creation. In order to guarantee that the content copied from the Model database are consistent , it’s important to have exclusive lock on the model database to prevent copying the data in, delete data from or change in schema of model database.
When we create a database, SQL Server automatically acquires exclusive lock on “model” database. If SQL Server fails to acquire exclusive lock then CREATE DATABASE will fail with following error:

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.

 

So while creating a database, we MUST NOT have any open connection to model database.

 

Regards

Rakesh Mishra

Like us on FaceBook Follow us on Twitter | Join the fastest growing SQL Server group on FaceBook

Follow me on TwitterFollow me on FaceBook

Data Platform Virtual Summit 2020

Subscribe to SQLServerGeeks YouTube channel. If you want more learning content in your inbox, subscribe to SQLServerGeeks Bulletin.

SQLServerGeeks YouTube | SQLServerGeeks Bulletin | SQLServerGeeks Twitter

3 Comments on “SQL Server Exclusive Lock on Database ‘model’”

  1. Open connections or open transactions? I don’t think an open connection would mess you up unless there was an uncommited transaction involving the model database.

    TGB

  2. Hi Thomas,

    It’s open Connection. If you have any connection open to model database then SQL Server can’y get exclusive lock on model and thus CREATE DATABASE will fail.

    HTH!
    Rakesh

  3. Hi Rakesh,

    I have the same error when creating a new database in my SQL Server 2008 R2 instance. I have looked through a couple of tech forums and tried the following suggested methods. So far nothing works for me, Short of restarting the SQL server machine which I have yet to try as it is a production machine. Any additional feedback would be appreciated.

    A) Disconnect and Reconnect your SQL Server Management Studio’s session. Your error will go away.

    B) — find the locks on the model db and kill them
    SELECT ‘KILL ‘ + CONVERT(varchar(10), l.request_session_id)
    FROM sys.databases d, sys.dm_tran_locks l
    WHERE d.database_id = l.resource_database_id
    AND d.name = ‘model’

    C) Reboot the SQL Server machine

    Rgds,

    Garion

Leave a Reply

Your email address will not be published. Required fields are marked *