Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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

or

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.

Also SET DEADLOCK_PRIORITY 10; doesn't help.

What else can I do? Thanks in advance.

share|improve this question

1 Answer 1

up vote 0 down vote accepted

Got it to work again. In my case following helped:

EXEC sp_who
KILL spid_taken_from_sp_who;

SET DEADLOCK_PRIORITY 10;
ALTER DATABASE [db_name] SET MULTI_USER WITH NO_WAIT 
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.