Getting Out of Single User Mode by Brute Force (T-SQL:SQL Server 2008)
Posted: October 16, 2012 Filed under: SQL Server 2008 Leave a commentI have an application (TRACKIT) database stays in single user mode. It failed to response to Alter Database myDB SET MULTI_USER command after many attemps.
Without stop the application, I find this solution posted by Dimitri Furman to use brute force to get back the control.
Here is the straightforward solution he provided.
From SSMS:
USE yourDBName
GO 1000
ALTER DATABASE yourDBName SET MULTI_USER
How long did it take: < 10 minutes.
The logic:
"After getting about 700 error messages saying that the database is in single user mode and can only be used by one user at a time,
the USE command succeeded, we got the only available session to ourselves, and put the database back into multi user mode."
http://blogs.msdn.com/b/dfurman/archive/2012/01/20/getting-out-of-single-user-mode.aspx