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.
| Author |
Topic |
|
jung1975
Aged Yak Warrior
503 Posts |
 Posted - 2002-08-01 : 11:06:10
|
| I would like to set up the automic job which can kill a spid that causing locking problem on database. --I usaully run sp_lock or sp_who to find the spid that causing a locking problem , and kill it.But, I would like to set up the automatic job using Stored procedure ---someting like this:CREATE PROCEDURE Kill_sp @spId INTASDECLARE @Cmd VARCHAR(30)SET @Cmd = 'KILL ' + CAST(@spId AS VARCHAR)EXEC(@Cmd)-----------------------------------------Does anyone running the automatic job to kill the spid that causing locking problem on database? Any information would be appreciated it!!Thank youJohn |
|
|
dsdeming
479 Posts |
Posted - 2002-08-01 : 21:31:01
|
| I've done what you're trying to do before, and there are a couple of things I would suggest.1. Run your spid killer as a scheduled task every x minutes.2. Maintain a table of persistent blocks that the spid killer updates.3. If one spid blocks another during consecutive runs of your spid killer ( every x minutes ), it's a candidate for the ax. However, before programmatically killing a process, which could be disastrous, I would find out as much as possible about it. Check for changes in cpu, io, and last batch on the blocking process. If those values are changing, I'd think twice before killing the spid.4. If you do kill a spid programmatically, insert everything you know about it into a logging table for later analysis.Anytime you run into poorly behaved processes, the most important thing is to identify them, analyze the code to determine the root cause, and correct it. That's the only way your system will ever be stable. In fact, if instead of a spid killer you implement a scheduled task that identifies persistent blocks and emails you, you'll find the problems much more quickly and save yourself a lot of headaches. |
 |
|
|
|
|
|
|
|
Automatic job to kill locking spid