Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Capture and Alert for Blocked SPIDS with SP_WHO2


Capture and Alert for Blocked SPIDS with SP_WHO2

Author
Message
jwalker8680
jwalker8680
Valued Member
Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)

Group: General Forum Members
Points: 54 Visits: 118
Comments posted to this topic are about the item Capture and Alert for Blocked SPIDS with SP_WHO2
Gary V. Garbers
Gary V. Garbers
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 11
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
SQLQuest29
SSC Eights!
SSC Eights! (972 reputation)SSC Eights! (972 reputation)SSC Eights! (972 reputation)SSC Eights! (972 reputation)SSC Eights! (972 reputation)SSC Eights! (972 reputation)SSC Eights! (972 reputation)SSC Eights! (972 reputation)

Group: General Forum Members
Points: 972 Visits: 4319
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
SQLQuest29
SSC Eights!
SSC Eights! (972 reputation)SSC Eights! (972 reputation)SSC Eights! (972 reputation)SSC Eights! (972 reputation)SSC Eights! (972 reputation)SSC Eights! (972 reputation)SSC Eights! (972 reputation)SSC Eights! (972 reputation)

Group: General Forum Members
Points: 972 Visits: 4319
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
jwalker8680
Valued Member
Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)

Group: General Forum Members
Points: 54 Visits: 118
Oops... See next post.

(PS> Anybody know how to delete your own post out here... <?>Wink
jwalker8680
jwalker8680
Valued Member
Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)

Group: General Forum Members
Points: 54 Visits: 118
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
Patti Johnson
SSC-Addicted
SSC-Addicted (473 reputation)SSC-Addicted (473 reputation)SSC-Addicted (473 reputation)SSC-Addicted (473 reputation)SSC-Addicted (473 reputation)SSC-Addicted (473 reputation)SSC-Addicted (473 reputation)SSC-Addicted (473 reputation)

Group: General Forum Members
Points: 473 Visits: 950
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
logicinside22
logicinside22
Right there with Babe
Right there with Babe (752 reputation)Right there with Babe (752 reputation)Right there with Babe (752 reputation)Right there with Babe (752 reputation)Right there with Babe (752 reputation)Right there with Babe (752 reputation)Right there with Babe (752 reputation)Right there with Babe (752 reputation)

Group: General Forum Members
Points: 752 Visits: 1396
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
logicinside22
logicinside22
Right there with Babe
Right there with Babe (752 reputation)Right there with Babe (752 reputation)Right there with Babe (752 reputation)Right there with Babe (752 reputation)Right there with Babe (752 reputation)Right there with Babe (752 reputation)Right there with Babe (752 reputation)Right there with Babe (752 reputation)

Group: General Forum Members
Points: 752 Visits: 1396
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
logicinside22
logicinside22
Right there with Babe
Right there with Babe (752 reputation)Right there with Babe (752 reputation)Right there with Babe (752 reputation)Right there with Babe (752 reputation)Right there with Babe (752 reputation)Right there with Babe (752 reputation)Right there with Babe (752 reputation)Right there with Babe (752 reputation)

Group: General Forum Members
Points: 752 Visits: 1396
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search