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?

score 13 · Accepted Answer · edited May 10 '21 at 07:29

13

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.

edited May 10 '21 at 07:29

Paul White

83,961
28
402
634

answered Sep 05 '18 at 13:23

sepupic

10,969
15
26

Be careful before you kill the session though - if model is locked due to a backup or some other system process, stop the session could do a lot of harm to your system. Check to see what user owns that session and what that user is doing before deciding whether to kill it or wait out the lock. – nateirvin Mar 18 '22 at 04:45
@nateirvin maybe you do not know the purpose of model db if you think that system processed could be interested in it, and you definitely have problems if you take a backup of system databases at work time and if it takes more then 1 second to backup model db – sepupic Mar 18 '22 at 05:51
I'm aware that backing up the model database is a little odd and should be a quick operation. I'm saying the last time I ran into this kind of situation, that's what was going on - the managed service provider we host with (AWS RDS in this case) was backing up all the DBs and that one locked for a little while. – nateirvin Mar 21 '22 at 13:48
I repeat you one more time: it's not a good idea to backup all the databases at working hours, it's no matter if it is you or AWS RDS to do it. It's up to you to complain with AWS RDS and to re-schedule it. – sepupic Mar 21 '22 at 15:11
How-to get more info about request_session_id ? – Kiquenet Oct 28 '22 at 11:32

Marcello Miorelli · Answer 2 · 2022-08-16T06:58:42.320

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.

dbid from sys.dm_exec_sql_text is not a reliable way to find people in a specific database. This can only return one value, but what happens if a user has connected to database A and is running a query that holds locks in databases B, C, and D? In some simple cases this may work, just be aware it won't be reliable all the time. — Aaron Bertrand, Sep 05 '18 at 12:37
Indeed, thank you, now I get the database name, if available, from sys.dm_exec_sessions — Marcello Miorelli, Aug 16 '22 at 07:01

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

2 Answers2

Linked