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.
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:
- 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.'
- 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)
24 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’;
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.
Thanks for this Pinal Dave
My pleasure @eintveit
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.
Thanks for sharing Ravjeet.
Thanks for sharing Pinal Dave, great help!
i tried the above code but i’m still getting the same error (sql version 11.0.3156.0)
Check who has connection to model and stop them.
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.
It means you are the one in model database. Rather than KILL, you need to change database context by using “USE master” command.
Thank you
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
….
Perfect solution, thank you Penal
This was helpful, thanks! one thing I would add is that in my scenario I built the create database script using the GUI, and still had the create DB window open. When i tried to run the T-SQL script, I got this error. When I closed the GUI window, the connection to model went away. thought it might be helpful if anyone else tried to run the T-SQL while the GUI window was still open.
Thank you so much for this solution. It helped resolve the problem I was having. Cheers!
Awesome Article. I got the same issue and it got fixed.
Pinal, thanks for the solution and also your responses in the comments section, as USE MASTER command worked for me.
Please select the master database and run the application, It will work
PInal, I just had our Deployment Guy come to me with this issue. Looks like our Monitoring program is the culprit. Will be changing the monitoring software to only look at USER db’s!
-Jim
Thanks so much.
I could not create any database. I will receive error 1807.
I executed the above script and solved the issue.
I have to say, kill the session works for my AG creation that blocked with the error
I am getting the same error, when I run the first script from SSMS it says model not in use. I can create a database from sqlcmd but not from SSMS. I tried uninstalling and installing SSMS, but still i am unable to create a database from SSMS and get this model-locked error. I even re-started sql services. I am running SQL 2022 developer on my local laptop.
Any help is much appriciated.
the first thing I’d do is qry sysprocesses to see what’s accessing model db. And most likely just kill that process as it’s hung.