9

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?

2 Answers 2

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.

5
  • 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, 2022 at 4: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, 2022 at 5: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, 2022 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, 2022 at 15:11
  • How-to get more info about request_session_id ?
    – Kiquenet
    Oct 28, 2022 at 11:32
0

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.

2
  • 2
    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. Sep 5, 2018 at 12:37
  • Indeed, thank you, now I get the database name, if available, from sys.dm_exec_sessions Aug 16, 2022 at 7:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.