Debugging table locks in NAV 2013 and later

dave_c
dave_c Member Posts: 46
edited 2015-03-18 in SQL Performance
What is the best way to find what process is locking a table in versions of NAV where the SQL access happens as the user running the SQL service? I guess you may be able to manage it with the SQL profiler with full SQL tracing turned on. However we have a customer who is having a lock happening once a week or so. We cannot leave the profiler running that long to find who started the blocking session, so what is the best approach?

Comments

  • kine
    kine Member Posts: 12,562
    Lock once a week... they are happy users and for me it is nothing to solve, it is standard behavior that sometime the lock is held longer than timeout. Or you mean "massive locking problem once per week, when system is unusable because all is locked by one user"?
    Kamil Sacek
    MVP - Dynamics NAV
    My BLOG
    NAVERTICA a.s.
  • NaviDeveloper_NL
    NaviDeveloper_NL Member Posts: 42
    You can use the Blocked Process Report feature.
  • dave_c
    dave_c Member Posts: 46
    kine wrote:
    Lock once a week... they are happy users and for me it is nothing to solve, it is standard behavior that sometime the lock is held longer than timeout. Or you mean "massive locking problem once per week, when system is unusable because all is locked by one user"?
    You are right, it not the most urgent issue in the world :). However when it is locked it's kept locked for 20 minutes or so, so it is a minor inconvenience. As much as anything I'm wondering so I know what to do next time it happens and it's more of an issue.

    I will look into the Blocked Process Report.
  • kine
    kine Member Posts: 12,562
    If it is locked for 20 minutes, than yes, it is something you need to look at. Good is, that you have around 18 minutes to find on which table the lock is, and than trying to find the user and ask him what he is doing. Just looking to the SQL Profiler and queries running against the DB could lead you to the process which is generating them (e.g. if it is about Requisition worksheet etc.). Or you can enable full SQL tracking to see the info about the user generating the queries...
    Kamil Sacek
    MVP - Dynamics NAV
    My BLOG
    NAVERTICA a.s.
  • davmac1
    davmac1 Member Posts: 1,283
    I had a customer where a long running block was caused by waiting for user action before the transaction was committed.
    In this case, a shipment would pop up an email that the user would then edit and send before the transaction was committed. Sometimes the user took off for lunch before the email screen popped up!
    Check if any of your transactions require user input before completing.
    David Machanick
    http://mibuso.com/blogs/davidmachanick/

Categories