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.