SQL Server 2008 R2 Stuck in Single User Mode
SqlSql ServerDatabaseSql Server-2008Database DeploymentSql Problem Overview
Having executed a DB deploy (from a VS SQL Server database project) on a local database, which failed, the database has been left in a state where it has single user mode left on (the deploy runs as single user mode).
When I connect to it from SSMS and try something like the following:
ALTER DATABASE MyDatabase
SET MULTI_USER;
GO
I get the error:
> Changes to the state or options of database 'MyDatabase' cannot be made at this time. The database is in single-user mode, and a user is currently connected to it.
I tried taking the database offline, which SSMS tells me succeeds, but it doesn't appear to actually do anything. So far, I've only been able to get around this by dropping and recreating the database (which is kind of okay, because it's only a local test database). However, I'd like to be able to reset the status.
How can I convince SQL Server to take this database out of single user mode?
Sql Solutions
Solution 1 - Sql
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 Master
ALTER DATABASE YourDatabase SET MULTI_USER
Solution 2 - Sql
Try the below commands
First run these three commands
USE [master]
SET DEADLOCK_PRIORITY HIGH
exec sp_dboption MyDBName, 'single user', 'FALSE';
Second run these two commands
ALTER DATABASE MyDBName SET MULTI_USER WITH NO_WAIT
ALTER DATABASE MyDBName SET MULTI_USER WITH ROLLBACK IMMEDIATE
Solution 3 - Sql
This was answered here, the code is:
use master
ALTER DATABASE YourDatabase SET SINGLE_USER WITH ROLLBACK IMMEDIATE
--do you stuff here
ALTER DATABASE YourDatabase SET MULTI_USER
Solution 4 - Sql
Use DAC (Dedicated Admin Connection). Make sure you have enabled it first
In SSMS type in admin:
Solution 5 - Sql
To force the update use " with rollback immediate"
ALTER DATABASE [DATABASE_NAME] SET MULTI_USER with rollback immediate