Being alerted when a certain stored procedure is blocked

Question

Being alerted when a certain stored procedure is blocked

Marios Philippopoulos

SSC Guru

Points: 57190
More actions
March 11, 2010 at 8:53 am

#386482

I would like to be alerted when a specific stored procedure in my database is blocked for more than, say, 60 seconds.
I can create a job that fires once a minute and runs a script to look for this blocking event; if the condition is found, it will send me an email.
I know how to do this, but my question is: is there a better way to do this in SQL 2008?
SQL Agent Alerts? Extended events? Any other feature I am unaware off?
Thanks for any feedback.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]

Viewing 5 posts - 1 through 4 (of 4 total)

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

dmoldovan SSCertifiable Points: 6334 More actions · Answer 1

See if these help:

http://sqlblogcasts.com/blogs/tonyrogerson/archive/2007/04/06/event-notifications-monitoring-blocked-processes-and-other-events-end-to-end-how-to-set-it-up-and-make-it-work.aspx

http://blogs.microsoft.co.il/blogs/dannyr/archive/2008/10/07/getting-blocking-info-in-sql-server-2005-amp-2008-the-easy-way.aspx

http://www.sqlservercentral.com/articles/Blocking/64474/

Marios Philippopoulos SSC Guru Points: 57190 More actions · Answer 2

dmoldovan (3/22/2010)

See if these help:

http://sqlblogcasts.com/blogs/tonyrogerson/archive/2007/04/06/event-notifications-monitoring-blocked-processes-and-other-events-end-to-end-how-to-set-it-up-and-make-it-work.aspx

http://blogs.microsoft.co.il/blogs/dannyr/archive/2008/10/07/getting-blocking-info-in-sql-server-2005-amp-2008-the-easy-way.aspx

http://www.sqlservercentral.com/articles/Blocking/64474/%5B/quote%5D

Thanks! I'll have a look at these links.

__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]

dmoldovan SSCertifiable Points: 6334 More actions · Answer 3

You're welcome.

Please share your solution with us, if you have time...

Marios Philippopoulos SSC Guru Points: 57190 More actions · Answer 4

dmoldovan (3/22/2010)
You're welcome.
Please share your solution with us, if you have time...

Will do, thanks again!

__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]