LLI Wiki — My Digital Knowledge Base

Error 1807: CREATE DATABASE fails on an exclusive lock on model

Find and end the session that holds the exclusive lock on the model database when CREATE DATABASE fails with error 1807.

CREATE DATABASE fails with error 1807, Could not obtain exclusive lock on database 'model'. Retry the operation later, when SQL Server cannot lock the model database exclusively. There are two usual causes:

  1. Another session has opened the model database exclusively.
  2. Several CREATE DATABASE statements run at the same time.

Find the session that holds the lock

Check whether another session uses the model database:

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';

If a session holds a lock, get its session ID:

SELECT request_session_id FROM sys.dm_tran_locks
WHERE resource_database_id = DB_ID('Model')

The query returns the session ID, for example 55.

Identify the statement

DBCC INPUTBUFFER returns the last statement sent by the session. Its EventInfo column contains the statement text.

DBCC INPUTBUFFER(55);

End the session or wait

Decide, from the statement, whether to wait for the session to finish or to end it. Ending a session rolls back its open transaction.

KILL 55;

Then run CREATE DATABASE again.

References

Mastodon LLIMastodon SocialFosstodon