Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Capture and Alert for Blocked SPIDS with SP_WHO2 Expand / Collapse
Author
Message
Posted Wednesday, April 6, 2011 2:12 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, May 31, 2013 6:57 AM
Points: 30, Visits: 111
Comments posted to this topic are about the item Capture and Alert for Blocked SPIDS with SP_WHO2
Post #1089075
Posted Wednesday, April 6, 2011 6:34 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, April 26, 2013 5:56 AM
Points: 3, Visits: 10
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.
Post #1089217
Posted Wednesday, April 6, 2011 11:52 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: 2 days ago @ 8:09 AM
Points: 736, Visits: 3,728
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
Post #1089472
Posted Wednesday, April 6, 2011 11:52 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: 2 days ago @ 8:09 AM
Points: 736, Visits: 3,728
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
Post #1089473
Posted Friday, April 8, 2011 7:16 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, May 31, 2013 6:57 AM
Points: 30, Visits: 111
Oops... See next post.

(PS> Anybody know how to delete your own post out here... <?>)
Post #1090602
Posted Friday, April 8, 2011 7:26 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, May 31, 2013 6:57 AM
Points: 30, Visits: 111
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.
Post #1090614
Posted Tuesday, June 14, 2011 6:29 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, August 20, 2014 10:08 AM
Points: 229, Visits: 805
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
Post #1124921
Posted Tuesday, January 10, 2012 8:08 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, August 5, 2014 1:46 PM
Points: 321, Visits: 1,279
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


Aim to inspire rather than to teach.
SQL Server DBA
Post #1233257
Posted Tuesday, January 10, 2012 8:47 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, August 5, 2014 1:46 PM
Points: 321, Visits: 1,279
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?


Aim to inspire rather than to teach.
SQL Server DBA
Post #1233295
Posted Wednesday, February 22, 2012 10:21 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, August 5, 2014 1:46 PM
Points: 321, Visits: 1,279
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?



Aim to inspire rather than to teach.
SQL Server DBA
Post #1256129
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse