SQL SERVER – FIX: Error 1807 Could not obtain exclusive lock on database ‘model’. Retry the operation later – Part 2

I have always mentioned in the blogs that the most sought out blogs would be around troubleshooting or around specific error messages. These blogs are the biggest source of page views from various search engines. I love getting these error messages and an opportunity for me to learn something new. Here is my first blog written in the year 2010 and I still get questions around the blog via emails. FIX: Error: 1807 Could not obtain exclusive lock on database ‘model’. Retry the operation later. Let us learn to fix error 1807 in this blog post.

The blog is still relevant in SQL Server 2016 and the basics don’t change much between versions. Here is the error 1807.

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.

SQL SERVER - FIX: Error 1807 Could not obtain exclusive lock on database 'model'. Retry the operation later – Part 2 model-in-use-01-800x169

Solarwinds

There is already a connect item on Microsoft site that you can use for reference.

And as per Microsoft:

The exclusive lock on the model database is a necessary step to create a new database. When SQL Server creates a new database, “The SQL Server uses a copy of the model database to initialize the database and its metadata”. Also, users could create, modify, drop objects in the Model database. As such, it is important to exclusively lock the model database to prevent copying the data in change from the Model database. Otherwise, there is no guarantee that the content copied from the Model database are consistent and valid.

Why I am Writing Part 2 for Error 1807?

I realized that the solution given in an earlier blog is not working for most of my readers. Earlier solution was “Disconnect and Reconnect your SQL Server Management Studio’s session.”

If there is someone else grabbing the connection to model database, then above would not work. So, we need to do below:

  1. Find who is having connection? Below query can help in that.
IF EXISTS (
		SELECT request_session_id
		FROM sys.dm_tran_locks
		WHERE resource_database_id = DB_ID('model')
		)
BEGIN
	PRINT 'Model Database in use!!'

	SELECT *
	FROM sys.dm_exec_sessions
	WHERE session_id IN (
			SELECT request_session_id
			FROM sys.dm_tran_locks
			WHERE resource_database_id = DB_ID('model')
			)
END
ELSE
	PRINT 'Model Database not in used.'
  1. Kill the connection. Below query would provide KILL command which we can run to kill ALL connections which are using model database.
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'

Now, I feel the answer is more complete and this error can be easily mitigated. Do let me know if you ever encountered this error in your environments. More you share, more we learn together.

Reference: Pinal Dave (https://blog.sqlauthority.com)

Solarwinds
, ,
Previous Post
PowerShell – Querying SQL Server From Command Line
Next Post
SQL SERVER – Configuration Manager – MMC could not create the snap-in. The snap-in might not have been installed correctly

Related Posts

14 Comments. Leave new

  • thanks for the tip Pinal!
    I’d add a DISTINCT so as to prevent duplicate spid’s on the list

    SELECT distinct ‘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 = ‘databasename’;

    Reply
  • I am getting this error on a new installation of SQL Server 2008 R2. When I run your code from above it shows that .Net SqlClient Data Provider is the problem.

    Reply
  • Christian E. Møller (@eintveit)
    January 30, 2017 6:15 pm

    Thanks for this Pinal Dave

    Reply
  • I got this error while I was trying to create a new database. There was an active backup command running which was causing this error. Once the backup finished, I could create the database.

    Reply
  • Thanks for sharing Pinal Dave, great help!

    Reply
  • Haider S Al-Khafagi
    December 6, 2017 9:30 am

    i tried the above code but i’m still getting the same error (sql version 11.0.3156.0)

    Reply
  • Its showing 102 process is there and when I try to kill that process it gives below error.

    Msg 6104, Level 16, State 1, Line 1
    Cannot use KILL to kill your own process.

    Reply
    • It means you are the one in model database. Rather than KILL, you need to change database context by using “USE master” command.

      Reply
  • Madhu Unnikrishnan
    November 24, 2019 1:48 pm

    Thank you

    Reply
  • I run this code every night from a batch file and sometimes it fails with the error Could not obtain exclusive lock on database ‘model’. should add the kill commands between the GO and IF statement or between GO and CREATE Statement below? Please Reply.

    Thank you!

    USE master
    GO

    IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE (‘[‘ + name + ‘]’ = ‘CustomersDB’ OR name = ‘CustomersDB’))
    BEGIN
    print ‘Database already exists, dropping database…’
    ALTER DATABASE CustomersDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE
    DROP DATABASE CustomersDB
    END
    GO

    CREATE DATABASE CustomersDB
    COLLATE SQL_Latin1_General_CP1_CI_AS
    GO

    USE CustomersDB
    ….

    Reply
  • Perfect solution, thank you Penal

    Reply

Leave a Reply

Menu