How to kill SPIDs

[OldSkoolSkater]

Hello

Running SQL7 sp3.
I am an admin of an inherited SQL server and have a question about process IDs.
I noticed that a scheduled job that truncates a table(through Enterprise Manager\Jobs) was taking far longer to complete than normal - hours rather than minutes, and when I looked into the problem I found that there were two SPIDs that were blocking/blocked against the database object.
One SPID was blocking on the table in question, and the other SPID was blocked by that SPID.

I tried to kill the blocking SPID through EM but it was a persistent little beggar and would not be killed off.
The only way I managed to kill it was to restart the SQLserver service (drastic, I know!)
The scheduled job then ran as it should.

Is there a better way of getting rid of blocking SPIDs, as this is an occasional problem that happens when you least want it to??

Cheers

Steve - Network Coordinating in the UK

 

[JayKusch]

Great Desciption ..."persistent little beggars"!!! I have come across SPIDs like this occassionally and have been left no other choice but to stop/start SQL Server.

Thanks

J. Kusch

 

[SQLRickster]

Stopping and Starting SQL Server is the only way to kill these types of "hung" spids as far as I know.

SQLRickster

 

[OldSkoolSkater]

Thanks to you both for replying!

I was hoping there was a way around this, but you've confirmed that there isn't....
Oh well....

Cheers

Steve - Network Coordinating in the UK