Autoclose for Databases - Part II
Introduction
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 progress :
·
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 ?
Solution?
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 Close just 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 will inherit the Auto Close option - which as Steve Jones' original
article pointed out, is not what we want.
Conclusion
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.
As Steve said
in his original
article :
"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.
Jon Reade
© Norb
Technologies, March 2003.
