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

Advertisements


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s