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.