Getting Out of Single User Mode by Brute Force (T-SQL:SQL Server 2008)


I 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



Leave a comment