kill connections to multiple databases in one shot

Question

kill connections to multiple databases in one shot

sqlguy80

SSCrazy

Points: 2763
More actions
March 16, 2017 at 7:17 pm

#319674

We have over 300 databases and folks keep sending data from all over the world from the front end app and mobile devices.
Which is the best , efficient and fastest way to kill all connections before we do a migration project scheduled this weekend?
I noticed that after the network guy stopped traffic from the web and I killed the connections I still saw some new connections coming after using the KILL command, so wondering.
The script I used is below:
USE master
GO
DECLARE @kill varchar(8000) = '';
SELECT @kill = @kill + 'kill ' + CONVERT(varchar(5), spid) + ';'
FROM master..sysprocesses
--WHERE dbid = db_id('MyDB')
EXEC(@kill);

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

andreas.kreuzberg SSCertifiable Points: 6183 More actions · Answer 1

Hi,
maybe this link can help you:
set database in single user mode
If you set the database in single user mode, all connections are "killed".
Best regards,
Andreas

Jeff Moden SSC Guru Points: 1003865 More actions · Answer 2

andreas.kreuzberg - Friday, March 17, 2017 1:13 AM
Hi,
maybe this link can help you:
set database in single user mode
If you set the database in single user mode, all connections are "killed".
Best regards,
Andreas

Just remember that if the session you're using that set it to single user loses connection, one of the web servers (or something else) will pick up the connection and then you're stuck. I'd set it to an administrative user and pray that no one was stupid enough to give any of the apps such privs.
The other thing you could do is disable all the logins except for those folks that will be working on the migration.

Of course, the best way to do it would be to setup a screen saying "Down for Maintenance" along with the time you're expected to have everything up and make sure the apps aren't able to connect.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

sean.buchanan4 SSC Journeyman Points: 90 More actions · Answer 3

The query you've mentioned should work, except it won't stop new SPIDs from connecting after you have run it. You may also have to wait a long time for any large transactions to rollback.

One way that I find quite useful is to set the database to restricted user mode. This will KILL any connections in the same manner as setting it to single user, but this way you can still have more than one connection to the database, but non administrative users will still not be able to connect. In addition, it'll rollback any 'in flight' transactions pretty fast.

ALTER DATABASE <DB_name> SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE;
GO

The only catch is if your app logins have db_owner permissions on the database, they can still connect. In which case, the easiest option may be to disable any logins in your database that have the db_owner database role, or the sysadmin server role.

Good luck!
--Sean Buchanan