Capture and Alert for Blocked SPIDS with SP_WHO2

Question

Capture and Alert for Blocked SPIDS with SP_WHO2

jwalker8680

SSC Eights!

Points: 858
More actions
April 6, 2011 at 2:12 am

#235085

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

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

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

Gary V. Garbers Valued Member Points: 71 More actions · Answer 1

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.

SQLQuest29 SSCrazy Eights Points: 8264 More actions · Answer 2

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 🙂

SQLQuest29 SSCrazy Eights Points: 8264 More actions · Answer 3

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 SSC Eights! Points: 858 More actions · Answer 4

Oops... See next post.

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

jwalker8680 SSC Eights! Points: 858 More actions · Answer 5

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.

Patti Johnson SSCertifiable Points: 7054 More actions · Answer 6

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

SQlLogic SSCrazy Eights Points: 9686 More actions · Answer 7

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

SQlLogic SSCrazy Eights Points: 9686 More actions · Answer 8

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?

SQlLogic SSCrazy Eights Points: 9686 More actions · Answer 9

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?