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'

No comments:

Post a Comment