Kill blocking spid automatically

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 blocking spid automatically

Author

Topic

Hommer
Aged Yak Warrior

808 Posts

Posted - 2005-03-15 : 10:50:33

Morning, everybody! I have a two-fold question to ask the group. Is it a good idea to kill blocking spid? And if it is, is there a way to automat the job?

As a database/application programmer, I know the ultimate way to eliminate the blocking is in the application code, and I am going after those code blocks, using performon/sql trace etc. at the meantime, I want to proactively response to the blocking problem.

Currently, when I receive calls from users about database hung-ups, I check blocking using sp_who, then use kill spid to end the blocking ones. I do realize some of these blocking will eventually get released, but some others will escalate into a mess.

I want to create this sql job that will check any blocking spid every 5 minutes, then when it found one that has been there for more then 3 minutes, kill it.

Is this a good idea, and how hard is it to implement?

Thanks!

X002548
Not Just a Number

15586 Posts

Posted - 2005-03-15 : 11:15:13

I found this lying around...as to whether it's a good idea...I'd say no



CREATE TABLE spid_Sleep (
  SPID varchar(50)
, Status varchar(50)
, Login varchar(50)
, HostName varchar(50)
, BlkBy varchar(50)
, DBName varchar(50)
, Command varchar(50)
, CPUTime varchar(50)
, DiskIO varchar(50)
, LastBatch varchar(50)
, ProgramName varchar(50)
, SPIDz  varchar(50)
)
GO

INSERT INTO spid_Sleep EXEC sp_who2

SELECT * FROM spid_Sleep WHERE Status = 'sleeping'

Brett

8-)

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-03-15 : 11:41:35

I vote no for "good idea"
Better check what is causing the blocking in more detail.
Perhaps lower the isolation level:
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITED
Use (NOLOCK) hint in the select's

A code snippet to start KILLING...

SELECT
	'KILL ' + LTRIM(spid)
FROM
	master.dbo.sysprocesses
WHERE
	spid > 50 -- 1-50 reserved system processes
	AND blocked > 0
	AND waittime/60000 >= 3 -- wait 3 minutes or more

Didn't someone here have a cool script to kill off processes, with a cool name
usp_terminate, usp_hastalavista or something like that...
there was a thread I'm sure

rockmoose

Lucifer79
Starting Member

1 Post

Posted - 2005-03-31 : 10:00:12

This procedure will help to kill all connections to a given databse

[url]http://www.databasejournal.com/img/usp_killDBConnections.sql[/url]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources