Database Stuck in Single User Mode

Question

Windows Server 2003 SP3 R2 64 bit

SQL Server 2008 R2 RTM

The database is in single user mode and is not comming back to multi-user mode:

The statement

select * from sys.dm_tran_locks where resource_database_id=13

shows that request_session_id = 29 holds a whole bunch of locks and is not releasing the locks. I tried stopping the SQL Server instance and even rebooting the server but still am unable to successfully execute the command,

ALTER DATABASE DB SET MULTI_USER WITH ROLLBACK IMMEDIATE

When I do sp_who2, I get the following for SPID = 29

SPID Status Login HostName BlkBy DBName Command CPUTime DiskIO LastBatch ProgramName SPID REQUESTID
29    BACKGROUND                     sa   .   . master DB STARTUP       2193329 14464419 04/26 23:56:14                                                29    0   

(Results from

select * from sys.dm_tran_locks where resource_database_id=13)

resource_type	resource_subtype	resource_database_id	resource_description	resource_associated_entity_id	resource_lock_partition	request_mode	request_type	request_status	request_reference_count	request_lifetime	request_session_id	request_exec_context_id	request_request_id	request_owner_type	request_owner_id	request_owner_guid	request_owner_lockspace_id	lock_owner_address
DATABASE		13		0	0	S	LOCK	GRANT	1	0	29	0	0	SHARED_TRANSACTION_WORKSPACE	0	00000000-0000-0000-0000-000000000000	0x00000000800F6280:0:0	0x000000008710C440
DATABASE		13		0	0	S	LOCK	GRANT	1	0	54	0	0	SESSION	0	00000000-0000-0000-0000-000000000000	0x00000000800F2380:0:0	0x0000000087109E00
EXTENT		13	1:1420304	0	0	X	LOCK	GRANT	0	33554432	29	0	0	TRANSACTION	0	00000000-0000-0000-0000-000000000000	0x00000000800F2380:2:1	0x00000000EC6A5140
OBJECT		13		2.12E+09	7	X	LOCK	GRANT	0	5.37E+08	29	0	0	TRANSACTION	0	00000000-0000-0000-0000-000000000000	0x00000000800F2380:2:1	0x00000000CAD6B500
OBJECT		13		2.12E+09	6	X	LOCK	GRANT	0	5.37E+08	29	0	0	TRANSACTION	0	00000000-0000-0000-0000-000000000000	0x00000000800F2380:2:1	0x00000000CAD6B540
OBJECT		13		2.12E+09	5	X	LOCK	GRANT	0	5.37E+08	29	0	0	TRANSACTION	0	00000000-0000-0000-0000-000000000000	0x00000000800F2380:2:1	0x00000000CAD6B580
OBJECT		13		2.12E+09	4	X	LOCK	GRANT	0	5.37E+08	29	0	0	TRANSACTION	0	00000000-0000-0000-0000-000000000000	0x00000000800F2380:2:1	0x00000000CAD6B5C0
OBJECT		13		2.12E+09	3	X	LOCK	GRANT	0	5.37E+08	29	0	0	TRANSACTION	0	00000000-0000-0000-0000-000000000000	0x00000000800F2380:2:1	0x00000000CAD6B600
OBJECT		13		2.12E+09	2	X	LOCK	GRANT	0	5.37E+08	29	0	0	TRANSACTION	0	00000000-0000-0000-0000-000000000000	0x00000000800F2380:2:1	0x00000000CAD6B640
OBJECT		13		2.12E+09	1	X	LOCK	GRANT	0	5.37E+08	29	0	0	TRANSACTION	0	00000000-0000-0000-0000-000000000000	0x00000000800F2380:2:1	0x00000000CAD6B680
OBJECT		13		2.12E+09	0	X	LOCK	GRANT	0	5.37E+08	29	0	0	TRANSACTION	0	00000000-0000-0000-0000-000000000000	0x00000000800F2380:2:1	0x00000000CAD6B6C0
OBJECT		13		2.12E+09	15	X	LOCK	GRANT	0	5.37E+08	29	0	0	TRANSACTION	0	00000000-0000-0000-0000-000000000000	0x00000000800F2380:2:1	0x00000000CAD6B340
OBJECT		13		2.12E+09	14	X	LOCK	GRANT	0	5.37E+08	29	0	0	TRANSACTION	0	00000000-0000-0000-0000-000000000000	0x00000000800F2380:2:1	0x00000000CAD6B380
OBJECT		13		2.12E+09	13	X	LOCK	GRANT	2	33554432	29	0	0	TRANSACTION	0	00000000-0000-0000-0000-000000000000	0x00000000800F2380:2:1	0x0000000087109F40
OBJECT		13		2.12E+09	12	X	LOCK	GRANT	0	5.37E+08	29	0	0	TRANSACTION	0	00000000-0000-0000-0000-000000000000	0x00000000800F2380:2:1	0x00000000CAD6B3C0
OBJECT		13		2.12E+09	11	X	LOCK	GRANT	0	5.37E+08	29	0	0	TRANSACTION	0	00000000-0000-0000-0000-000000000000	0x00000000800F2380:2:1	0x00000000CAD6B400
OBJECT		13		2.12E+09	10	X	LOCK	GRANT	0	5.37E+08	29	0	0	TRANSACTION	0	00000000-0000-0000-0000-000000000000	0x00000000800F2380:2:1	0x00000000CAD6B440
OBJECT		13		2.12E+09	9	X	LOCK	GRANT	0	5.37E+08	29	0	0	TRANSACTION	0	00000000-0000-0000-0000-000000000000	0x00000000800F2380:2:1	0x00000000CAD6B480
OBJECT Thank you. +ive		13		2.12E+09	8	X	LOCK	GRANT	0	5.37E+08	29	0	0	TRANSACTION	0	00000000-0000-0000-0000-000000000000	0x00000000800F2380:2:1	0x00000000CAD6B4C0

Answer 1

What happened with this database and server before coming into this state, if anything at all?

---

Henrik Fyhn http://repeatableread.blogspot.com/

Answer 2

The database was in suspect mode.

I followed the steps here to recover from suspect mode. However, the database could not come out of single user mode. I had to finally reboot the database server. The database recovered and then came back online.

+ive