Error 1807 Sql Server 2008
Contents |
KamathDecember 23, 20141 0 0 0 After installing a SQL server 2008 R2 instance SQL_SERVER\MSSQLSERVER2008R2 side by side on the Box which already has SQL 2005 production instance, we are could not obtain exclusive lock on database 'model' sharepoint unable to create a database from the management studio Receiving the create database failed. some file names listed could not be created. check related errors. below error Create failed for Database ‘x'. (Microsoft.SqlServer.Smo) -------------------- ADDITIONAL INFORMATION: An exception occurred while executing sql server error 5030 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. sp_who2 parameters Check related errors. (Microsoft SQL Server, Error: 1807) Cause: By design when we create a database we need exclusive lock on model. When SQL Server creates a new database, "The SQL Server uses a copy of the model database to initialize the database and its metadata". Also, users could create, modify, and drop objects in the Model database.
The Database Could Not Be Exclusively Locked To Perform The Operation
As such, 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. We need to check what is taking the lock on model database We ran sp_who2 and found a couple of spids using model with .Net SQLClient Data Provider" as the application According to http://blog.sqlauthority.com/2010/01/01/sql-server-fix-error-1807-could-not-obtain-exclusive-lock-on-database-model-retry-the-operation-later/ - we tried to Disconnect and Reconnect your SQL Server Management Studio's session. Your error will go away.But this wasn't working for us. Checked the other basic things-SQL startup account was Local System-We had permissions on the folder-Same file names were not being used-They have 6.36TB free in the drive where we create the database. We also found that in order to create a database on the SQL 2005 prod instance we need to turn off SQL 2008 instance (Strange!) #--How to identity: Use master GO IF EXISTS(SELECT request_session_id FROM sys.dm_tran_locks WHERE resource_database_id = DB_ID(‘Model'))
SERVER - FIX: Error 1807 Could not obtain exclusive lock on database ‘model'. Retry the operation later – Part 2 May 25, 2016Pinal DaveSQL2 commentsI have always mentioned in the blogs that the most sought out blogs would be around troubleshooting or around specific error messages. These blogs
Sql Create Database
are the biggest source of page views from various search engines. I love getting these error messages and an opportunity for me to learn something new. Here is my first blog written in the year 2010 and I still get questions around the blog via emails. FIX: Error: 1807 Could not obtain exclusive lock on database ‘model’. Retry the operation later. Let us learn to fix error 1807 in this blog post.The blog is https://blogs.msdn.microsoft.com/poojakamath/2014/12/23/could-not-obtain-exclusive-lock-on-database-model-retry-the-operation-later-create-database-failed/ still relevant in SQL Server 2016 and the basics don’t change much between versions. Here is the error 1807.Msg 1807, Level 16, State 3, Line 1 Could not obtain exclusive lock on database ‘model’. Retry the operation later. Msg 1802, Level 16, State 4, Line 1 CREATE DATABASE failed. Some file names listed could not be created. Check related errors. There is already a connect item on Microsoft site that you can use http://blog.sqlauthority.com/2016/05/25/sql-server-fix-error-1807-not-obtain-exclusive-lock-database-model-retry-operation-later-part-2/ for reference.And as per Microsoft:The exclusive lock on the model database is a necessary step to create a new database. When SQL Server creates a new database, "The SQL Server uses a copy of the model database to initialize the database and its metadata". Also, users could create, modify, drop objects in the Model database. As such, 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.Why I am Writing Part 2 for Error 1807?I realized that the solution given in an earlier blog is not working for most of my readers. Earlier solution was “Disconnect and Reconnect your SQL Server Management Studio’s session.”If there is someone else grabbing the connection to model database, then above would not work. So, we need to do below:Find who is having connection? Below query can help in that. IF EXISTS ( SELECT request_session_id FROM sys.dm_tran_locks WHERE resource_database_id = DB_ID('model') ) BEGIN PRINT 'Model Database in use!!' SELECT * FROM sys.dm_exec_sessions WHERE session_id IN ( SELECT request_session_id FROM sys.dm_tran_locks WHERE resource_database_id = DB_ID('model') ) END ELSE PRINT 'Model Database not in used.' Kill the connection. Below query would provide KILL command which we can ru
ASP.NET MVC | Online Interviews | Top Performers | DNF MVP | Top Posts | Winners | Subscribe | Bookmarks | Catalogs Welcome Guest ! Register Login Home > Forums > Error and Solution > Loading ... SQL Server : http://www.dotnetfunda.com/forums/show/3024/sql-server-when-creating-a-new-database When creating a new database... Posted by PandianS under Error and Solution https://sqljourney.wordpress.com/2010/06/15/fixing-error-message-1807-could-not-obtain-exclusive-lock-on-database-model-in-sql-server/ on 12/5/2010 | Points: 10 | Views : 7314 | Status : [Member] [MVP] | Replies : 2 Write New Post | Search Forums | Resolved Posts | Un Answered Posts | Forums Home [Resolved] Many of us may experience this error when creating a new Database..Msg 1807, Level 16, could not State 3, Line 1Could not obtain exclusive lock on database 'model'. Retry the operation later.Root Cause: Root cause of this error may be one of the following reason 1. Somebody exclusively open the Model database in different session 2. If more than one create database statements attempt to run simultaneouslyHow to identity:Use master GOIF EXISTS(SELECT request_session_id FROM sys.dm_tran_locksWHERE resource_database_id = DB_ID('Model')) PRINT could not be 'Model Database being used by some other session'ELSE PRINT 'Model Database not used by other session' So we can identify that whether the Model database being execlusively used by any session.., If you found anybody using the Model database execlusively, You can identify what kind of statement being used by the session...using the script given belowSELECT request_session_id FROM sys.dm_tran_locksWHERE resource_database_id = DB_ID('Model')The script will return the session ID (i.e: 53)We have to identity what kind of query/activity being performed by this session(53).DBCC InputBuffer(53)EventInfo column returns the query performed, Based on that, you have to decide whether the Session ID(53) can be Aborted/ Not. If you want to abort the session(53), run the commandKill 53Now you can try to create a new Database..! Cheers www.SQLServerbuddy.blogspot.com iLink Multitech Solutions Reply | Reply with Attachment Alert Moderator Responses Posted by: Vuyiswamb on: 12/5/2010 [Member] [MVP] [Administrator] NotApplicable | Points: 25 0 Nice, thanks for sharingThank you for posting at Dotnetfunda [Administrator]PandianS, if this helps please login to Mark As Answer. | Alert Moderator Posted by: Ravidnrabarange on: 7/15/2016 [Member] Starter | Points: 25 0 Thanks it worked
2013(2) January 2013(2) December 2012(3) June 2011(1) August 2010(2) July 2010(4) June 2010(13) RSS - PostsRSS - Comments Your Email ID: Join 266 other followers SQL Journey SQL Journey Top Rated Blogroll Exam Material for 70-463 SSIS Team Blog SSIS Talk SSIS Junkie Jessica M. Moss Community SQL Server Learning Center Syntax Highlighter SSIS Community Tasks and Components SQL Server QA Projects and Tools Samples Design Center Database Modelling Samples Bookmarks Blog Stats 144,330 hits Get a Freelancer (function(d) { var po = d.createElement('script'); po.type = 'text/javascript'; po.async = true; po.src = '//static.flnaffiliate.com/build/js/affiliate-sdk.js'; var s = d.getElementsByTagName('script')[0]; s.parentNode.insertBefore(po, s); })(document); « UNIQUE Key Constraint with Multiple NULL values in SQLServer Insert Excel Data into a SQL Server table usingOPENROWSET » Fixing Error Message 1807 could not obtain exclusive lock on database ‘model' in SQLServer Posted by Prashant on June 15, 2010 Some days back I got this question from one of my friend, when he was trying to create a New Database, he is getting the below error message. Msg 1807, Level 16, State 3, Line 1 Could not obtain exclusive lock on database ‘model'. Retry the operation later. Msg 1802, Level 16, State 4, Line 1 CREATE DATABASE failed. Some file names listed could not be created. Check related errors.
The Situation: He was trying to create table on Model database first then he tried to create a New Database. As Model database is used as a template for a database being created, SQL Server tries to obtain an Exclusive Lock on Model database. So if one session is using model database SQL Server can’t obtain exclusive lock on model. If you are using SQL Server Management Studio try these steps: Step1: Open a New Query (say SQL Query1.sql) and select “model” from database dropdown box. Step2: Now in another