Login to Members Area

Choose the service you want to log in
     
24x7x365 Presence
1-888-289-2246
Live Chat

Hosting Blog

 

How to kill Database Connections

If you see your database is making too many connections, then use the query below to kill all connections in one go.

=============

USE master
GO
DECLARE @dbname varchar(30), @spid varchar(10), @start datetime
SELECT @start = current_timestamp, @dbname = 'airtel_sms'

-- Timeout after 5 mts
while(exists(Select * FROM sysprocesses WHERE dbid = db_id(@dbname)) AND
datediff(mi, @start, current_timestamp) < 5)
begin
DECLARE spids CURSOR FOR
SELECT convert(varchar, spid) FROM sysprocesses
WHERE dbid = db_id(@dbname)
OPEN spids
while(1=1)
BEGIN
FETCH spids INTO @spid
IF @@fetch_status < 0 BREAK
exec('kill ' + @spid)
END
DEALLOCATE spids
END

Dipesh Wednesday 12 June 2013 - 8:49 pm | | Default

No comments

(optional field)
(optional field)

Comment moderation is enabled on this site. This means that your comment will not be visible until it has been approved by an editor.

Remember personal info?
Small print: All html tags except <b> and <i> will be removed from your comment. You can make links by just typing the url or mail-address.