SQL Server Club
Home Articles Essential Guides Blogs Links Twitter Facebook Join Contact About
Fixing the Error Message - Server: Message 1807, Level 16, State 2 Line 1
Web Monkey About the Author

With security clearance and well over a decade of experience with SQL Server, Web Monkey is the resident DBA at Norb Technologies and a real database guru.

A regular contributor to SQL Server Club, his background includes software development, hardware, a smattering of essential networking knowledge and an unhealthy interest in web security. He has an obsession with automating repetitive tasks, but has a tendency to forget where he left his last open bag of fruit and nut...

Typepad Profile RSS Feed Web Monkey on Twitter
Send to a Friend Printer Friendly Version
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 SQL Server 2000 database, and 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 didn'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 that 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, and 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?

The 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.

If you experience error 1807, remember that it's probably an open connection to the model database 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.
Send to a Friend Printer Friendly Version
Top of Page
© Norb Technologies 2007-2010 Privacy Policy Norb Technologies Devdex Feedback Home
Feedback Form