SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

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.

Comments

Leave a comment on the original post [martincatherall.com, opens in a new window]

Loading comments...