Database in Single User Mode – Resolution

When the database gets stuck in Single_User mode, try these options to bring it back to Multi_User mode!

• T-SQL statement to change it to MULTI_USER

ALTER DATABASE [StuckDB] SET MULTI_USER WITH NO_WAIT
ALTER DATABASE [StuckDB] SET MULTI_USER WITH ROLLBACK IMMEDIATE

• You will not able to complete above query if there is any active connection on the Database. Find active connection process and kill it before changing it to Multi_User. You can find action connections to the database using below query

sp_who2 
select spid from master..sysprocesses where dbid = DB_ID('my_db')

• If you are not able to find any active connections in sp_who2 or sys.processes, disconnect other connections to the Server as those might be using server.db.table format to access the database.

• If this is experiencing Deadlock issues, there is a good article, please refer it @ http://www.sqlservercentral.com/blogs/pearlknows/2014/04/07/help-i-m-stuck-in-single-user-mode-and-can-t-get-out/

• If the Database connection is released and taken by a different process, following simple trick may help: ( Ref: http://www.sqlservercentral.com/Forums/Topic1189873-391-1.aspx)

USE My_DB
GO 1000

• If you are not able to kill other process, try connecting using Dedicated Admin Connection (Ref: http://social.msdn.microsoft.com/Forums/sqlserver/en-US/68e1dc32-7019-4a64-80cc-6e311f1f308d/how-to-get-database-out-of-single-user?prof=required)

-Vijred

Advertisements
This entry was posted in knowledge, Script, SQL, SQL Error and tagged , , , . Bookmark the permalink.

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