Kill or Close all connected user from Sql Server 2008 2012 Database

In some cases DBA’s need to kill or Close all currently used Sql Server database connections for attaching or detaching Database, making DB/database read only, performing maintenance tasks etc. For such type of issues DBA wants to get exclusive access to the database. To do so, you can set the database to Single User Mode, which permits only one database connection at a time. At that moment if any other user trying to connect/access then they will get an error. So lets start to learn “How to disconnect all users from SQL Server Database 2010 2012“.

To bring a database to the single user mode, use the following query:

ALTER DATABASE DATABASENAME SET SINGLE_USER

Users those already connected to the db/database, they will not be disconnected. The ‘SET SINGLE_USER’ command will wait until the other users disconnected. If you want to override this command and forcefully disconnect other users, then use the following query:

ALTER DATABASE DATABASENAME SET SINGLE_USER WITH ROLLBACK IMMEDIATE

OK now your database immediately move to the single user mode. After completion of your maintenance task you need to go back to multi user mode by applying another TSQL command which is given below:

ALTER DATABASE DATABASENAME SET MULTI_USER

Caution: Don’t forget to set again the Database to Multi User mode after your maintenance work.

Posted in Database, SQL Server

Leave a Reply

Your email address will not be published. Required fields are marked *

AlphaOmega Captcha Classica  –  Enter Security Code
     
 

*