Lost session after putting DB in single user mode

sql-server-2008

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.

Also SET DEADLOCK_PRIORITY 10; doesn't help.

What else can I do? Thanks in advance.

Best Answer

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

Related Solutions

Sql-server – Cannot add witness to the mirroring setup

Can you attach the SQL Profiler to all three instances involved and monitor for these events:

Then attempt again to establish the mirroring session. Make sure you select all columns when adding the events. Start from a blank template.

2011-12-09 20:04:07.983  Database Mirroring Connection    Connected   
2011-12-09 20:04:08.133  Audit Database Mirroring Login   Login Success
2011-12-09 20:04:27.980  Database Mirroring State Change DBM: 
  Synchronized Principal without Witness -> 
  DBM: Synchronizing Principal             
2011-12-09 20:04:28.237  Database Mirroring State Change DBM: 
  Synchronizing Principal -> 
  DBM: Synchronized Principal without Witness
2011-12-09 20:05:42.530  Database Mirroring Connection   An error occurred...

Here is the explanation for what happens:

at 20:04:07 the principal connect with the witness
at 20:04:08 the principal and witness finish a successful handshage (audit login succes)
at 20:04:27 DBM changes the state from synchronized w/o witness to synchronized
at 20:04:28 DBM changes the state back from syncronized to synchronized w/o witness
at 20:05:42 the connection with the witness times out due to inactivity and closes

This sequence of events indicates that the connectivity between principal and witness is functional. The request to add the witness fails, and there could be multiple reasons. Similar events must occur on the mirror and on the witness as well, is not clear why you say that you could only capture them on the principal.

SQL Server 2008 R2 database stuck in single user mode

Take a quick look at the SPID

USE master 
EXEC sp_who

From there work out the db and kill the corresponding lock.

Switch from single to multi-user mode

ALTER DATABASE 'DB NAME' SET MULTI_USER WITH ROLLBACK IMMEDIATE