Killing 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

Transact-SQL (2000)

Killing Connections

Author

Topic

ggarza75
Yak Posting Veteran

50 Posts

Posted - 2009-01-08 : 15:24:20

Can someone help me with the syntax to kill connections.

I have user1 making several connections to DB1. How can I kill those multiple user1 connections?

Thanks.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-01-08 : 15:56:34

KILL spidNO, such as KILL 75. Check sp_who, sp_who2, or sysprocesses for the current activity.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

ggarza75
Yak Posting Veteran

50 Posts

Posted - 2009-01-08 : 16:01:38

quote:
Originally posted by tkizer

KILL spidNO, such as KILL 75. Check sp_who, sp_who2, or sysprocesses for the current activity.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

What if that one account has several spids?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-01-08 : 16:03:00

Then kill them all.

KILL 75
KILL 90
KILL 110

Are you looking for a script that'll find them all for you?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

ggarza75
Yak Posting Veteran

50 Posts

Posted - 2009-01-08 : 16:09:58

quote:
Originally posted by tkizer

Then kill them all.

KILL 75
KILL 90
KILL 110

Are you looking for a script that'll find them all for you?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

For early Saturday morning, I want to schedule a job to kill all that user1 connections. This user1 account has several connections. Without having to see what SPIDS this account has, I wanted to kill all connections the user1 account has open without having to specify the SPID since there will be many spids.

Thanks.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-01-08 : 16:18:34

Here's a sample script that I use in a job, modify as needed:


DECLARE @spid varchar(10)

SELECT @spid = spid
FROM master.sys.sysprocesses
WHERE dbid IN (DB_ID('db1'), DB_ID('db2')

WHILE @@ROWCOUNT <> 0
BEGIN
 EXEC('KILL ' + @spid)

 SELECT @spid = spid
 FROM master.sys.sysprocesses
 WHERE 
  dbid IN (DB_ID('db'1), DB_ID('db2') AND
  spid > @spid 
END

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

ggarza75
Yak Posting Veteran

50 Posts

Posted - 2009-01-08 : 16:19:48

Thank you very much

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-01-08 : 16:43:15

You're welcome.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources