Post reply

blocking

  • February 20, 2014 at 1:33 pm

    #289656

    Hi,

    I want to implement a script which will run continuously to find out blocked sessions and send an email when ever it finds the blocking. Please advise.

    Thanks

  • February 20, 2014 at 1:58 pm

    #1690888

    Mani-584606 (2/20/2014)

    Hi,

    I want to implement a script which will run continuously to find out blocked sessions and send an email when ever it finds the blocking. Please advise.

    Thanks

    My advice is to use Google/Bing. A simple search with these words got several very relevant postings: sql server blocked session send email

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • February 20, 2014 at 2:06 pm

    #1690893

    You might want to source the problem instead of just sending an email, try to create a table to write what procedure is running so that you can try to catch the culprit and then fix the problem, you might end up having tons of emails if you look for blocked sessions all the time 🙂

  • April 14, 2014 at 3:19 pm

    #1705932

    I mostly agree with some of the previous posters that you can Google efficient ways to do this. Given that, here is something I created with some research on this site that I posted a few years ago. It is effective and you can modify it to your specific needs. Note, I developed his for a 2000 SQL Server but the concepts still apply. It requires creating a table, and SP to populate it and a job to schedule it. The code is all there for each step. Hope it helps:

    http://www.sqlservercentral.com/Forums/Topic645081-146-2.aspx#bm647193

  • April 15, 2014 at 7:09 am

    #1706078

    Hi,

    you can use dbWarden to get an email when there are blockings:

    http://sourceforge.net/projects/dbwarden/

    regards,

    Andreas

  • April 15, 2014 at 1:06 pm

    #1706226

    First, understand that blocking is normal, it's kind of like the locking latch on a bathroom stall door. There are 100+ different reasons why a process can be temporarily blocked. A process can even block itself.

    The following article described how to analyze wait statistics at a high level, and that's probably where you want to start.

    http://www.sqlskills.com/blogs/paul/wait-statistics-or-please-tell-me-where-it-hurts/

    Only when a process has been in a blocked state for a significant percentage of it's duration or when blocking turns into a high level of queuing is there a reason to drill down on why specific processes are blocked. For this you can look at sys.dm_exec_requests, which gives you wait_type, wait_time, blocking_session_id (if it's blocked by another session's locking), etc.

    "Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

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

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