SQL Server-create database failed

Error messages as below:

Could not obtain exclusive lock on database 'model'. Retry the operation later.

CREATE DATABASE failed. Some file names listed could not be created. Check related errors. (Microsoft SQL Server, Error: 1807)

 

From: https://blogs.msdn.microsoft.com/poojakamath/2014/12/23/could-not-obtain-exclusive-lock-on-database-model-retry-the-operation-later-create-database-failed/

Cause:

By design when we create a database we need exclusive lock on model. 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, and 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.

 

Solutions:

（1）use below query to check is model db is being used by other sessions:

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'

（2）you can also check what kind of statements  are using model

SELECT request_session_id FROM

sys.dm_tran_locks WHERE resource_database_id =DB_ID('Model');

DBCC INPUTBUFFER(spid)

（3）kill those spid to release the session and then you can create a new database

kill spid