Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Autoclose for Databases - Part II

By Jon Reade,

Autoclose for Databases - Part II

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.

 

 

Total article views: 5502 | Views in the last 30 days: 2
 
Related Articles
FORUM

Created database but used Model

new database was created with Model selected as db rather than Master

FORUM

Creating database tables from models

How to create a database when given the model - usually with nested entities and inheritance

FORUM

Database modeling tools

Checking for errors in database model

FORUM

Error while moving model and msdb system databases.

Error while moving model and msdb databases to new location.

FORUM

Error Message when Generating Model of Database

I received an error message when generatnig a model of my database.

Tags
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones