Monday, September 2, 2013

remove single user mode from a database

How to remove single user mode from a database?


Step1: Fine out the processes, which are using the particular database say ‘AdventureWorks’. This can be find executing below SP
Sp_who2

By executing sp_who2, get the SPID’s which uses particular database



Step 2: Kill that process as below

KILL [SPID]
KILL 52 --For example, if 52 is the SPID, that is using database AdventureWorks

Step 3: Execute Below query

ALTER DATABASE [DatabaseName] SET SINGLE_USER WITH ROLLBACK IMMEDIATE

Step 4: Set multiple user mode on database using below query


ALTER DATABASE [DatabaseName] SET MULTI_USER