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)