CREATE DATABASE – I’ve not seen that before.

G’day,

It’s been a while :)

I thought that I’d make a quick blog post about an incident that I encountered today that I had not come across before.
I was working on my laptop when I tried doing a really basic task – creating a database.
So I keyed in

CREATE DATABASE [TEST];

I received the error
Msg 1807, Level 16, State 3, Line 3
Could not obtain exclusive lock on database ‘model’. Retry the operation later.
Msg 1802, Level 16, State 4, Line 3
CREATE DATABASE failed. Some file names listed could not be created. Check related errors.

Now, as we all know, model is used as a template for all new databases so the error kind of made sense, but it still threw me a bit.
I’m 100% sure that I’m the only person using the SQL Instance on my laptop.

So, I began looking through the multiple windows that I had open.
And then I found a SSMS query window that I’d been running a cursor in, which dynamically changed the database context. The window was set to “model”

I made a note of the SPID and quickly queried sys.dm_exec_sessions to see if I had any more windows open that were connected to ‘model’ – I didn’t have any.
So, I changed the context of the offending window to ‘tempdb’ – then I tried to create the database again.

CREATE DATABASE [TEST];

This time it worked perfectly (as expected).
I then dropped the database and tried to re-create it again with another SSMS window connected to model.
I checked for blocking – and noticed that SQL was indeed attempting to acquire an exclusive database lock on model – makes sense.
As a final test, I rebooted the SQL instance and my laptop and tried the process of creating a database again while having another SSMS window connected to ‘model’
Sure enough, I got the same results

Msg 1807, Level 16, State 3, Line 3
Could not obtain exclusive lock on database ‘model’. Retry the operation later.
Msg 1802, Level 16, State 4, Line 3
CREATE DATABASE failed. Some file names listed could not be created. Check related errors.

It’s probably reasonably intuitive to come to the conclusion that SQL Server doesn’t want anybody creating a copy of model when somebody else is potentially altering it – hence the exclusive lock that is needed.

Thanks for reading.

Have a great day.

Cheers

Martin.

About Martin Catherall

Martin Catherall is Senior SQL consultant at SQL Down Under, based in Melbourne, Australia. Martin is also a Microsoft Data Platform MVP and Regional Mentor (RM) for the Professional Association for SQL Server. Prior to relocating to Australia he was extremely active in the Christchurch, New Zealand data community - founding the local SQL Server user group and organizing SQL Saturday Christchurch - which later became SQL Saturday South Island. He likes learning interesting stuff about processing and storing data and passing on his knowledge. In his spare time he likes to learn guitar and hang out with his two young sons Callum Glen and Robert Michael.