How to drop/kill all connections to a database from T-SQL

October 27, 2010 20:43 by Duncan Grist

A very quick, but hopefully very useful blog post from me today.

If you ever find the need to drop all connections to a database, either to delete it, perform maintenance or for any other reason, you can do it in a couple of commands straight from T-SQL code:

ALTER DATABASE [%DATABASENAME%] SET READ_ONLY WITH ROLLBACK IMMEDIATE;ALTER DATABASE [%DATABASENAME%] SET READ_WRITE WITH ROLLBACK IMMEDIATE;

In order of the database server to change the access policy over to read only it must immediately kill all active connections that were made using the old access policy, which just so happens to be all active connections. The proceeding command restores the original access policy of the database.

Preventing new connections

Frequently, when you have the need to kill all active connections, a way of stopping any new connections from connecting for a period of time would also be useful.

This can be achieved by executing:

ALTER DATABASE [%DATABASENAME%] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

and to allow new connections again:

ALTER DATABASE [%DATABASENAME%] SET MULTI_USER WITH ROLLBACK IMMEDIATE;

I hope you find these tips useful. That's all for now.

Tags: , ,

hidden features

Add comment



(Will show your Gravatar icon)



  Country flag

biuquote
  • Comment
  • Preview
Loading



CAPTCHA image




Schema Inspector

Tag cloud

Calendar

<<  August 2014  >>
MoTuWeThFrSaSu
28293031123
45678910
11121314151617
18192021222324
25262728293031
1234567

View posts in large calendar