SQL Server 2008 R2 Stuck in Single User Mode

sql sql-server database sql-server-2008 database-deployment

In first run following query in master database

exec sp_who

If you can't find the culprit, try

SELECT request_session_id FROM sys.dm_tran_locks WHERE resource_database_id = DB_ID('YourDatabase')

Then kill all process that use your database with following query:

KILL spid

Then run following query:

USE MasterALTER DATABASE YourDatabase SET MULTI_USER

sql sql-server database sql-server-2008 database-deployment

Try the below commands

First run these three commands

USE [master] SET DEADLOCK_PRIORITY HIGHexec sp_dboption MyDBName, 'single user', 'FALSE';

Second run these two commands

ALTER DATABASE MyDBName SET MULTI_USER WITH NO_WAITALTER DATABASE MyDBName SET MULTI_USER WITH ROLLBACK IMMEDIATE

sql sql-server database sql-server-2008 database-deployment

This was answered here, the code is:

use masterALTER DATABASE YourDatabase SET SINGLE_USER WITH ROLLBACK IMMEDIATE --do you stuff here ALTER DATABASE YourDatabase SET MULTI_USER

CodeHunter