How to identify which table causes Blocking locks?

  • There are plenty of situations where we identify the queries causing the blocking locks and based on the query we get the table involved etc. In case where two or more tables are involved, how can we pin point this is the one table that causes blocking locks. Any queries... Any techniques... Any explanations...

    Give me a fish, you feed me for a day! Teach me to fish, you feed me for a life time.

  • Do you mean deadlocks? Blocking is the purpose of locks and while it should be minimized it is necessary for integrity.

  • I did not mean deadlocks. I meant blocking locks. BTW, not all locks are blocking locks. A lock becomes blocking lock only when another process is looking for the same resource and the process holding the lock blocks it.

    What i want to know how to identify the table that causes the blocking lock.

    Give me a fish, you feed me for a day! Teach me to fish, you feed me for a life time.

  • There's plenty of script on the site to check for blocking and to return the information on the blocking spid, for example http://www.sqlservercentral.com/scripts/Lock+and+Connection+Management/30800/[/url] (you might want to remove the kill within this one).

    There are others that make use of the cross apply syntax within 2005. Do a quick search on "blocking chain"



    Shamless self promotion - read my blog http://sirsql.net

  • Justin (12/17/2008)


    I did not mean deadlocks. I meant blocking locks. BTW, not all locks are blocking locks. A lock becomes blocking lock only when another process is looking for the same resource and the process holding the lock blocks it.

    What i want to know how to identify the table that causes the blocking lock.

    Okay, I have seen many people think a block is a deadlock and they are 2 different things.

    I would disagree a little bit on your definition of a blocking lock, mainly in semantics, because every lock is designed to be a blocking lock.

    I think Nicholas has really pointed you in the right direction.

  • Hi!

    Here ...

    http://dynamicsuser.net/blogs/stryk/archive/2008/11/03/blocks-amp-deadlocks-in-nav-with-sql-server.aspx

    ... I have described how I deal with blocks & deadlocks; which processes I establish to monitor, analyze, etc. including several TSQL Code templates.

    The examples given there are all related to Dynamics NAV, but I guess this does not really matter with this issue.

    Hope this helps a little.

    Kind regards,

    Jörg

    Jörg A. Stryk
    MVP - MS Dynamics NAV

  • For that u have to run query

    select * from sys.sysprocesses

    when u run the qury, u will be find waitresources, it will be like 12:12344:778

    From that u have to analize by DBName:Tablename(objectname)

    so

    select db_name(12)

    select object_name(12344)

    _____________________________________________________________________________________________________________
    Paresh Prajapati
    ➡ +919924626601
    http://paresh-sqldba.blogspot.com/[/url]
    LinkedIn | Tweet Me | FaceBook | Brijj

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

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