Capture and Alert for Blocked SPIDS with SP_WHO2

  • Comments posted to this topic are about the item Capture and Alert for Blocked SPIDS with SP_WHO2

  • Is it possible to get the script as a text file instead of clipboarding and loosing the formatting and special characters? It appears there are imbedded CR/LF and I loose the indenting when clipboarding either directly or from a PDF version. The script looks exactly what I need to resolve a couple of problems at the moment.

  • 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 🙂

  • 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 🙂

  • Oops... See next post.

    (PS> Anybody know how to delete your own post out here... <?>)

  • SQLQuest29 (4/6/2011)


    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.

    If you want to do it that way... You can check out this article on it http://www.sqlservercentral.com/articles/Blocking/64474/

    The way I did it was originally for a database used by a third party software that tends to have a lot of blocking that locks up the entire system.

    We don't have access to the code to fix where the real problem exists.

    I also include non-SQL people in the alerts and the details so they can share that information with the vendor... I even included a stored procedure for them so they can kill the blocking process in their database if the DBA is unavailable.

    If your system can't handle running this every 2 minutes... you desperately need new hardware.

    The entire process completes before I can take my finger off the mouse after clicking execute. (Registers 0 time and runs so fast it doesn't even register a blip on the processor/memory)

    The only reason I put it as long as 2 minutes is that I know if I get two emails in a row it's an issue.

  • Hey guys, I've been looking for a script like this so thank you very much.

    I was curious...can this script run on sql 2000?

    Thanks Patti

  • Hi SQLQuest29

    I would like to know how did you setup EVENT_Notification

    for BLOCKED_PROCESS_REPORT to find out when there is real blocking. can you explain me in detail . i want to do setup kind of you explained on prod server. if you helps would be appreciated.

    Mahalo

  • thanks for nice script to be posted here...

    few questions like to ask .

    how we can change time to run every 5 or 10 min instead of to run 2 min?

    DBMail script have to run separately?

    or how to run full procedure and which parameter have to pass?

  • I have put this script on prod environment and working fine giving me nice alert in e-mail.

    But some time when i check error log and i found the deadlock during that time which i can not see using this script why so?

    is there any different notification using this script and by turning on Trace Flags?

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply