SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by Joshua A. Walker / Capture and Alert for Blocked SPIDS with SP_WHO2 / Latest Posts

RE: Capture and Alert for Blocked SPIDS with SP_WHO2

logicinside22 — Wed, 22 Feb 2012 10:21:39 GMT

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?

RE: Capture and Alert for Blocked SPIDS with SP_WHO2

logicinside22 — Tue, 10 Jan 2012 08:47:06 GMT

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?

RE: Capture and Alert for Blocked SPIDS with SP_WHO2

logicinside22 — Tue, 10 Jan 2012 08:08:42 GMT

Hi SQLQuest29 I would like to know how did you setup EVENT_Notificationfor 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

RE: Capture and Alert for Blocked SPIDS with SP_WHO2

Patti Johnson — Tue, 14 Jun 2011 06:29:09 GMT

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

RE: Capture and Alert for Blocked SPIDS with SP_WHO2

jwalker8680 — Fri, 08 Apr 2011 07:26:00 GMT

[quote][b]SQLQuest29 (4/6/2011)[/b][hr][quote][b]jwalker8680 (4/6/2011)[/b][hr]Comments posted to this topic are about the item [B]<A HREF="/scripts/sp_who2/72823/">Capture and Alert for Blocked SPIDS with SP_WHO2</A>[/B][/quote]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.[/quote]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.

RE: Capture and Alert for Blocked SPIDS with SP_WHO2

jwalker8680 — Fri, 08 Apr 2011 07:16:05 GMT

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

RE: Capture and Alert for Blocked SPIDS with SP_WHO2

SQLQuest29 — Wed, 06 Apr 2011 11:52:04 GMT

[quote][b]jwalker8680 (4/6/2011)[/b][hr]Comments posted to this topic are about the item [B]<A HREF="/scripts/sp_who2/72823/">Capture and Alert for Blocked SPIDS with SP_WHO2</A>[/B][/quote]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.

RE: Capture and Alert for Blocked SPIDS with SP_WHO2

SQLQuest29 — Wed, 06 Apr 2011 11:52:04 GMT

RE: Capture and Alert for Blocked SPIDS with SP_WHO2

Gary V. Garbers — Wed, 06 Apr 2011 06:34:50 GMT

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.

Capture and Alert for Blocked SPIDS with SP_WHO2

jwalker8680 — Wed, 06 Apr 2011 02:12:10 GMT

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