jwalker8680 (4/6/2011)
Comments posted to this topic are about the item <A HREF="/scripts/sp_who2/72823/">Capture and Alert for Blocked SPIDS with SP_WHO2</A>
Very good effort and nice script !
Just to add my 2 cents,
It is not good to run the job every 2 mins (or any interval) on a highly busy prod server as it has its own overhead.
I had this type of job running and emailing the blocking details, but have switched to EVENT_Notification
which uses BLOCKED_PROCESS_REPORT. This is smart enough to fire when there is real blocking (and this can be adjusted using sp_configure). This can fire a sql agent job to insert the xml generated by BLOCKED_PROCESS_REPORT into a db table and if you want more fancy reports, you can email it by shredding the xml.
______________________________________________________________________________________________________________________________________________________________________________________
HTH !
Kin
MCTS : 2005, 2008
Active SQL Server Community Contributor 🙂