Database is in Single_User Mode and How to Change to Multi_User Mode


For unknown reason (due to operation failure), a database was stuck in Single_User mode.

Here are a few steps to kill the troubled session and change the database back to multi_user mode.

–1.Find the session_id to kill
Select request_session_id From sys.dm_tran_locks Where resource_database_id=DB_ID(‘DBName_In_Single_User_Mode’);

–2: Kill the found session_id

Kill theseessionid ;

–3. set the database to multi_user mode
USE [master]

ALTER DATABASE [DBName_In_Single_User_Mode] SET MULTI_USER WITH ROLLBACK IMMEDIATE;

Advertisements


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s