Disconnect All Users From Ms Sql Server 2005/2008 Database

To larn exclusive access to the database to perform maintenance tasks etc. you lot tin forcefulness out laid the database to Single User Mode, which permits entirely ane database connexion at a time. At that minute if other users endeavour to access the database piece you lot are working on that active connection, they volition have an error.

To convey a database to the unmarried user mode, role the next query:

ALTER DATABASE DATABASENAME SET SINGLE_USER

Users those already connected to the db when you lot run this command, they volition non hold upwards disconnected. Instead the 'SET SINGLE_USER' ascendence volition aspect till the others accept disconnected.

If you lot desire to override this scenario in addition to forcefully disconnect other users, in addition to thus role the next query:

ALTER DATABASE DATABASENAME SET SINGLE_USER WITH ROLLBACK IMMEDIATE

OK at nowadays your database directly motility to the unmarried user mode.

Now After completion of your maintenance chore you lot demand to larn dorsum to multiuser trend past times applying unopen to other TSQL ascendence which is given below:

ALTER DATABASE DATABASENAME SET MULTI_USER

If you lot larn the next error:

"Msg 5064, Level 16, State 1, Line 1 Changes to the soil or options of database '___' cannot hold upwards made at this time. The database is inwards single-user mode, in addition to a user is currently connected to it. Msg 5069, Level 16, State 1, Line 1 ALTER DATABASE tilt failed."

Use SP_Who (or SP_Who2 for to a greater extent than detailed info) to uncovering the session ID of the active connexion to the database:

EXEC sp_who

- or -

EXEC sp_who2

Terminate the connexion with:

KILL [session id]

Then produce the Alter Database ascendence you lot tried.

Set Offline

Set Offline prevents anyone, fifty-fifty administrators, from re-connecting:

use master copy modification database MyDatabase laid offline alongside rollback immediate

Using SQL Server Management Studio (SQL SMS)

When you lot correct click on a database in addition to click Tasks in addition to and thus click Detach Database, it brings upwards a dialog alongside the active connections.

By clicking on the hyperlink nether "Messages" you lot tin forcefulness out kill the active connections. You tin forcefulness out in addition to thus kill those connections without detaching the database.

More information on how to Kill All Processes That Have Open Connection inwards a SQL Server Database using SQL Server Management Studio here.