Create Database fails with the error "Could not obtain exclusive lock on database 'model'. Retry the operation later." | SansSQL

Tuesday, April 16, 2013

Create Database fails with the error "Could not obtain exclusive lock on database 'model'. Retry the operation later."

Sometimes when you try to create a database, the operation fails by presenting the below error message.
TITLE: Microsoft SQL Server Management Studio
------------------------------
Create failed for Database 'Test'.  (Microsoft.SqlServer.Smo)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1600.1+((KJ_RTM).100402-1539+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Create+Database&LinkId=20476
------------------------------
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
Could not obtain exclusive lock on database 'model'. Retry the operation later.
CREATE DATABASE failed. Some file names listed could not be created. Check related errors. (Microsoft SQL Server, Error: 1807)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1600&EvtSrc=MSSQLServer&EvtID=1807&LinkId=20476
------------------------------
BUTTONS:
OK
------------------------------

This happens because, the exclusive lock on the model database is a mandatory step that database engine takes to create a new database. We all know that when SQL Server creates a new database, it uses a copy of the model database to initialize the new database and its metadata. Also, users could create, modify, drop objects in the Model database. So, it is important to exclusively lock the model database to prevent copying the data in change from the Model database. Otherwise, there is no guarantee that the content copied from the Model database are consistent and valid.

To fix this issue, find out the connection that is using model database and close it and then re-issue the create database statement.
SELECT * FROM sys.sysprocesses WHERE DB_NAME(dbid)='model'

2 comments:

Genious Person said...

Thanks for the blog filled with so many information. Stopping by your blog helped me to get what I was looking for. Now my task has become as easy as ABC. Folderlock Regarding PC Windows 7 Totally free Download - Don't Spend Your Time Looking, Experience All About Cellular Desktop computers In this article how to encrypt a file folder in xp

Genious Person said...

Folder Lock For PC Ziddu - Don't Spend Your Time Looking, Study Information On Cellular Desktops On this page aaron

Post a Comment