Colin Phillips (Alumni)
A while ago I was trying to create a farm at a client site and ran into an issue where I couldn’t create the farm because I would receive the error message “Could not obtain exclusive lock on database ‘model’. Retry the operation later.” Just to note, after having gotten past this step (which I’ll detail below), I continued to get this same error message when trying to create other SharePoint databases (such as during the creation of service applications, and even new content databases).
Below is the error message I was seeing when trying to create a new farm: (Could not obtain exclusive lock on database ‘model’)
I started by trying to follow this blog post (http://dbasqlserver13.blogspot.ca/2013/10/error-1807-could-not-obtain-exclusive.html). Solution #1 didn’t work for me. And actually, Solution #2 didn’t exactly work for me either, but I found a way (detailed later) to get it to work for me.
[Start Referenced Blog Post]
Solution 1:
Disconnect and Reconnect your SQL Server Management Studio’s session. Your error will go away.
Solution 2:
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 simultaneously
How to identity:
#1:
Use master
GO
IF EXISTS(SELECT request_session_id FROM sys.dm_tran_locks
WHERE resource_database_id = DB_ID(‘Model’))
PRINT ‘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 exclusively used by any session.., If you found anybody using the Model database exclusively, You can identify what kind of statement being used by the session…using the script given below
#2
SELECT request_session_id FROM sys.dm_tran_locks
WHERE 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).
#3
DBCC InputBuffer(53)
The 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 following command
#4
Kill 53
Now you can try to create a new Database..!
[End Referenced Blog Post]
The above didn’t quite work for me as it kept running into a lock on the full text search. To get around this, I actually had to time things right by waiting for any new processes to start up, and then as quickly as possible killing them, followed by rerunning the new-spconfigurationdatabase command. By timing it right, there was a long enough window available to create the new database, before the next process kicked off for the full text search. I know what you’re going to ask next – why didn’t you just kill the full text search? Well, I did, but that didn’t stop the event from kicking off every 3 or 4 mins regardless. I think their SQL server was haunted – but I didn’t have permissions to do anything about that.
Nevertheless, essentially what I did (which finally worked) was as follows.
- Run the steps in #2 above to determine the list of any processes currently running (there can be several at once)
- Kill any currently running processes (using #4 above)
- Run step #2 above for a couple minutes until a new process (or processes) popped up
- As quickly as possible after a new process spins up, kill that process as well
- As quickly as possible after having killed the newest process, kick off the “new-spconfigurationdatabase”
I had to do these same steps whenever trying to create a new service application that had to create a SharePoint database, or a new content database.
Hopefully this helps anyone else that may be running into the issue.