Database Stuck in a Single User Mode in SQL Server

Database Stuck in a Single User Mode in SQL Server

第一步

By using the the dynamic view "sys.dm_tran_locks" we can find out the active sessions of the database which is stuck in a single user mode as follows:

--Query to find the session_id

SELECT request_session_id FROM sys.dm_tran_locks 

WHERE resource_database_id = DB_ID('YourDatabaseName') 

第二步

Now kill the session ID found by above query as follows:

-- kill all the processes which are using your database with following query:

    KILL spid

第三步

Bring the database into a multi_user mode by using following query:

USE Master
GO
ALTER DATABASE YourDatabaseName SET MULTI_USER WITH ROLLBACK IMMEDIATE
GO

第四步

Bring the database online.

USE Master
Go
ALTER DATABASE YourDatabaseName SET online
Go

第五步

Check the status of the database by using following Query and your Database should be back to normal / multi user mode and online.

select * from sys.databases where name ='YourDatabaseName'

 

作者:Chuck Lu    GitHub