Recently one of our customer called in for help as his database got stuck in SINGLE_USER mode after he tried to copy database using Copy Database wizard.
Using detach and attach method in database copy wizard does not allow any user connections to the source database thus puts the database in single_user mode in initial phase of SQL agent job execution. In case job fails in initial stages, database may get stuck in single_user mode.
When we tried to bring it in multi_user mode, it was failing as there was active connection and our process was being chosen as deadlock victim. The trick here is to increase the deadlock priority of command setting database in multi_user mode which worked.
Below command can be used in such scenario.
USE [master]
SET DEADLOCK_PRIORITY HIGH
ALTER DATABASE [<DBName>] SET MULTI_USER WITH NO_WAIT
ALTER DATABASE [<DBName>] SET MULTI_USER WITH ROLLBACK IMMEDIATEHope this article helps you in similar situations!
Happy Learning!!