I have same problem as here: SQL2005 Stuck in Single Mode User
Only difference, I am using SQL Server 2008. Tried everything, nothing works. When I run my
ALTER DATABASE [CS_10.1] SET MULTI_USER
ALTER DATABASE [CS_10.1] SET MULTI_USER WITH ROLLBACK IMMEDIATE
after killing the connection I get the following message:
Msg 1205, Level 13, State 68, Line 1 Transaction (Process ID 56) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction. Msg 5069, Level 16, State 1, Line 1 ALTER DATABASE statement failed.
A new sleeping connection to the single user database is immediately recreated after killing.
SET DEADLOCK_PRIORITY 10; doesn't help.
What else can I do? Thanks in advance.