DATABASE IS STUCK IN SINGLE_USER MODE!!!!!!!!!!!! |
|
So you needed to put a DB in single_user mode but then some process comes along and grabs the connection out from under you. I had this happen to me and I sure felt helpless. The issue I was running into was that when I tried to put the DB into multi_user mode it would deadlock the transaction and SQL would kill my connection. Found a ton of blogs with a lot of varying degrees of answers. Here is what worked for me. I set the Deadlock Threshold to high for the connection and ran the alter statement. Here is the code below. Use which ever Alter statement works for you.
USE [master]
SET DEADLOCK_PRIORITY HIGH
ALTER DATABASE <Your DB> SET MULTI_USER WITH NO_WAIT
ALTER DATABASE <Your DB> SET MULTI_USER WITH ROLLBACK IMMEDIATE
USE [master]
SET DEADLOCK_PRIORITY HIGH
ALTER DATABASE <Your DB> SET MULTI_USER WITH NO_WAIT
ALTER DATABASE <Your DB> SET MULTI_USER WITH ROLLBACK IMMEDIATE
How to Start SQL Server in Single-User Mode
-m parameter starts SQL Server in single-user mode.
Here is a great article explaining that. http://www.sqlservercentral.com/blogs/mssqlfun/2014/09/04/how-to-start-sql-server-in-single-user-mode-/ |
|