Kill connections - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Kill connections

Author

Topic

jackstow
Posting Yak Master

160 Posts

Posted - 2002-03-25 : 12:01:52

Is there a proc that will kill all the active connections to a db sitting in the connection pool?

nr
SQLTeam MVY

12543 Posts

Posted - 2002-03-25 : 12:24:18

It's easy to write one yourself.
Connections can be obtained from

select spid from master..sysprocesses where dbid = object_id('dbname')

then just execute a kill command for each one (except system processes).
Then execute a delay and check if the spids have all died. They may need to complete a task/rollback before dropping.

==========================================
Cursors are useful if you don't know sql.
Beer is not cold and it isn't fizzy.

jackstow
Posting Yak Master

160 Posts

Posted - 2002-03-26 : 04:10:15

I've tried to write a proc that will kill all the connections for a particular login;

DECLARE @spid int
DECLARE spid_cursor CURSOR FOR
SELECT spid
FROM master.dbo.sysprocesses
WHERE loginame = 'web_login'
OPEN spid_cursor
FETCH NEXT FROM spid_cursor INTO @spid
WHILE @@FETCH_STATUS = 0
BEGIN
KILL @spid
FETCH NEXT FROM spid_cursor INTO @spid
END
CLOSE spid_cursor
DEALLOCATE spid_cursor

..yes I know its got a cursor (!) I'm just looking for a quick fix for testing - but it seems you can't assign a variable to the KILL command - is that right?

nr
SQLTeam MVY

12543 Posts

Posted - 2002-03-26 : 08:07:00

yep - and why write a cursor if you want 'a quick fix for testing'?
It's always worth running something like this with the exec replaced by a print command first.

DECLARE @spid int ,
@sql varchar(1000)
select @spid = 0
while exists
(
select *
from master.dbo.sysprocesses
WHERE loginame = 'web_login'
and spid > @spid)
begin
select @spid = min(spid)
from master.dbo.sysprocesses
WHERE loginame = 'web_login'
and spid > @spid

select @sql = 'kill ' + convert(varchar(10),@spid)
exec (@sql)
end

==========================================
Cursors are useful if you don't know sql.
Beer is not cold and it isn't fizzy.

jackstow
Posting Yak Master

160 Posts

Posted - 2002-03-26 : 08:50:26

Works a treat. Thanks.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources