Today, I was testing something in the SQL Server and while trying to create the database, I got below error.
Msg 1807, Level 16, State 3, Line 2
Could not obtain exclusive lock on database 'model'. Retry the operation later.
Msg 1802, Level 16, State 4, Line 2
CREATE DATABASE failed. Some file names listed could not be created. Check related errors.
We all know that the model database is used for creating the database. If you want to have some objects being part of new database, we can put in the model. During the database creation, sql serve would copy the model and create the new one and then run the alter database to make to the size you specify in the create database syntax.
However, it is new to me that the create database statement require hold the exclusive lock on the model db, it does make sense so because we don’t want other processes is changing the model database while we are creating the new database.
To reproduce this error, just open any process and issue ‘use model’ and in another session , run the create database statement.
In below screenshot, the first top output is from sp_who2, you can see, spid 54 is in mode database and it is blocking the spid 54 ‘Create database’.
The 2nd part of output is from sys.dm_os_waiting_tasks, the spid 60 is waiting for the LCK_M_X . LCK_M_X is the exclusive locks .