Could not obtain exclusive lock on database 'model' - who is using it?

Question

when trying to run the following query in order to create a new database:

    CREATE DATABASE [Lunch]
     CONTAINMENT = NONE
     ON  PRIMARY 
    ( NAME = N'Lunch', 
FILENAME = N'E:\Data Files\Lunch.mdf' , 
SIZE = 110592KB , FILEGROWTH = 1048576KB ), 
     FILEGROUP [DATA] 
    ( NAME = N'Lunch_Data', 
FILENAME = N'E:\Data Files\Lunch_Data.ndf' , 
SIZE = 110592KB , FILEGROWTH = 1048576KB ), 
     FILEGROUP [NONCLUSTERED_INDEXES] 
    ( NAME = N'Lunch_nonclusteredindexes', 
FILENAME = N'E:\Data Files\Lunch_nonclusteredindexes.ndf' , 
SIZE = 110592KB , FILEGROWTH = 1048576KB )
     LOG ON 
    ( NAME = N'Lunch_log', 
FILENAME = N'F:\logFiles\Lunch_log.ldf' , 
SIZE = 524288KB , 
FILEGROWTH = 524288KB )
    GO

Getting the following error message:

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.

what is using the model database and does not allow me to obtain the exclusive lock?

Answer 1

Your model database is locked now so what you should do is to find out the session that has a lock on it, this can be done using sys.dm_tran_locks:

select request_session_id
from sys.dm_tran_locks
where resource_type = 'database' and
      resource_database_id = 3 and
      request_type = 'LOCK' and
      request_status = 'GRANT';

You can have a user that just opened an SSMS query window with the context of model database and did not ever execute any query there, but that session still holds S lock on the database resource.

Once you have found the locking session, just kill it.

Answer 2

I have been using the script below, based on the answer above by sepupic.

This is particularly useful when you need all users off an specific database, and that's why I have this parameter @dbname

Even if someone just opens SSMS and connect to a database it will show up in this query.

    DECLARE @dbname SYSNAME =NULL

SELECT 
        sdes.session_id 
       ,sdes.login_time 
       ,sdes.last_request_start_time
       ,sdes.last_request_end_time
       ,sdes.is_user_process
       ,sdes.host_name
       ,sdes.program_name
       ,sdes.login_name
       ,sdes.status

       ,sdec.num_reads
       ,sdec.num_writes
       ,sdec.last_read
       ,sdec.last_write
       ,sdes.reads
       ,sdes.logical_reads
       ,sdes.writes
       
       ,DatabaseName = COALESCE( db_name(sdes.database_id),  N'')
       ,sdest.ObjName
    ,sdes.client_interface_name
    ,sdes.nt_domain
    ,sdes.nt_user_name
    ,sdec.client_net_address
    ,sdec.local_net_address
    ,sdest.Query
    ,KillCommand  = 'Kill '+ CAST(sdes.session_id  AS VARCHAR)

from sys.dm_tran_locks t
INNER JOIN sys.dm_exec_sessions sdes
        ON T.request_session_id = sdes.session_id

Alternatively I have also done this in the past:

SET DEADLOCK_PRIORITY HIGH
USE [master]
ALTER DATABASE [model] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
USE model
GO

don't forget to change the database back to MULTI_USER:

ALTER DATABASE model SET MULTI_USER WITH ROLLBACK IMMEDIATE 

and this has so far solved for me this problem of Could not obtain exclusive lock on database 'model'.

After that I go back and run again my create database script and it runs successfully.