Fixing Error Message 1807 could not obtain exclusive lock on database ‘model’ in SQL Server

Some days back I got this question from one of my friend, when he was trying to create a New Database, he is getting the below 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.

The Situation:

He was trying to create table on Model database first then he tried to create a New Database. As Model database is used as a template for a database being created, SQL Server tries to obtain an Exclusive Lock on Model database. So if one session is using model database SQL Server can’t obtain exclusive lock on model.

If you are using SQL Server Management Studio try these steps:

Step1: Open a New Query (say SQL Query1.sql) and select “model” from database dropdown box.

Step2: Now in another Query (say SQL Query2.sql) run the below query:

select * from sys.dm_tran_locks

Observation: You will find result like below.

(Click on the image to enlarge)

This indicates Shared Lock has been Granted on resource_database_id = 3 (database_id: 3 is for model)

Step3: Now try to create database either from Object Explorer of SQL Server Management Studio or by CREATE DATABASE statement.

Step4: This time run query in step-3 and observe the result.

(Click on the image to enlarge)

This indicates an Exclusive lock on model database but has not been granted yet (it’s in WAIT state).

Solution:

Make sure to disconnect all the sessions which uses model database then CREATE DATABASE statement later. If this problem still remains, restart management studio and try again.

This is tested in SQL Server 2008, also leave your suggestions.

Check existence of Table or Database before creating it in SQL Server

To check if a table is exists or not a database:

--Check if table exists
IF EXISTS (SELECT 1 FROM sys.tables WHERE name = 'TableName')
PRINT 'TableName available.'
ELSE
PRINT 'TableName not available.'

Similar way we can also check if a database is exists or not before creating.

--Check if database exists
IF EXISTS (SELECT 1 FROM sys.databases WHERE name = 'DBName')
PRINT 'DBName available.'
ELSE
PRINT 'DBName not available.'

Please leave your valuable comments on this.