July 12, 2008 at 10:45 pm
Hi, could any one please get me a script that checks for blockings on a database and kills the required spid? the script has to be included in a job that runs say every one hour?
thanks
July 13, 2008 at 4:53 am
You can use sp_who2 to get all processes and any that are blocking will be in there. There are several variations of how to handle that output to get the distinct list of blockers. However, I would highly recommend not randomly killing processes just because they are blocking.
The better approach would be to get the sqltext for those processes so that you can determine how to make the queries a whole lot more efficient and then eliminate the blocking. If they are in fact not valid queries then find out where they are from and eliminate them from being executed.
Hopefully this makes sense.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
July 13, 2008 at 5:36 am
thanks for the reply David! I got your point made, yes its definetly important to check the queries that are running against the SPID before killing it and check to optimize the queries...but..I was thinking other than sp_who2, would there be any script that checks for the blockings? like select * from sysprocess where blocked > 0 would be fine, but then, and kill it simultaneously? how can that made be possible..its just for my knowledge purpose..thanks
July 16, 2008 at 8:13 am
Try sp_blocker_pss80:
http://support.microsoft.com/kb/271509
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply