I was answering a question
posed by a trainee DBA, who asked about an odd error she was getting when
trying to create a new database, one I'd not experienced before.
The error was :
Server: Msg 1807, Level 16, State 2, Line 1 Could not obtain exclusive lock on database 'MODEL'. Retry the operation later.
Looking this up in Books
Online doesn't help much, nor was there much out on the web.
So I started investigating…
As you may know, when SQL
Server creates a new database, it uses the model database as
a "template", which determines the data structures, initial file sizes and a
few other things, for the new database that is being created.
Whether you use Enterprise
Manager, or the T-SQL CREATE DATABASE command (which is what executes in the
background when you use the Enterprise Manager GUI to create a new
database), SQL Server attempts to obtain an EXCLUSIVE lock on the model
database. Presumably this is to
prevent other processes from updating the model database's schema whilst the
new database is being created, so that the new database's schema is in a
known, consistent state.
You can see this behavior in
- Open a Query Analyzer window and select the model database in the drop down.
- Create a new database, either in Enterprise Manager or with the CREATE DATABASE command, and click on OK or execute it.
- Open a new window in Query Analyzer, and execute an sp_lock - you'll see an 'X' in the 'mode' column, against the model database's database ID (I guess this is 3 on all installations, but execute a select name, dbid from master..sysdatabases if you want to check this)
You'll also get the Error
1807 message which sparked off this article.
However, through trial and
error, I found that if you have even a single connection open to the model
database, it is not possible for SQL Server to obtain this exclusive lock.
This can be caused by something as simple as having the model database
selected in the database drop-down in Query Analyzer. This prevents the
CREATE DATABASE command from creating a new database.
Another reason is that if you
have previously opened the model database in Enterprise Manager, then closed
it, the connection to the database
remains open, which means that the Create Database command cannot
acquire the exclusive access lock that it requires to execute successfully.
Not so bad if you've just done it, but how about if it was opened and closed
three months back ?
What has this got to do with
Auto Close option? Well, if you have configured model to 'Auto Close' after
opening it, then it will close, at least in Enterprise Manager, and prevent
you from experiencing this error. So it might be very tempting to set Auto
on model to avoid encountering error 1807.
But don't reach for that
mouse just yet. Here's the real gotcha :
Remember we said that SQL
Server uses model as a template for every new database?
Well, that includes all of
the database options - including Auto Close.
So if you set the Auto Close
option to be on for the model database, every new database you create
original article pointed out, is not what we want.
If you experience error 1807,
remember that it's probably an open connection to the model db that's
causing it. Drop the connection and try again. But don't be tempted to set
the Auto Close option on model - at some point you'll forget you did it and
all of your new databases will have it set, unless you manually reset it for
each of them.
"If the database isn't used
much, it probably isn't taking many resources and isn't worth setting this."
It's not - so don't.
© Norb Technologies, March