Identifying and logging database locks

sorenhinrupsorenhinrup Member Posts: 136
Hi

Do you know a workaround or script to implement in a NAV2017, where we can log who has created a deadlock for other users?
Right now we can use sp_who/sp_who2 when it happends, but this only gives us the servicetier account, not the actual user of NAV.

My thought is that, this relation has to be somewhere in the db, because we can see the following in the database lock funktion through the development client:
pl81wvsx66x8.png

Answers

  • SanderDkSanderDk Member Posts: 497
    For help, do not use PM, use forum instead, perhaps other people have the same question, or better answers.
  • sorenhinrupsorenhinrup Member Posts: 136
    That is the one I put into my post. I'm aware of this exists, but I need a log of who locks who through a period. This is only visible when the lock is active.
  • bbrownbbrown Member Posts: 3,268
    Your title says "database locks" but your post says "deadlock". Those are not the same thing. Which is it you are trying to investigate? Looking at the error message will tell you which you are dealing with. I often find it's a combination of both. Which you are dealing with also drives your investigation path and solutions.

    Unfortunately there is no direct link from SQL back to a specific NAV user. The only "user" that ever touches SQL is the service account. Thus all activity is show as coming from that user. The investigation becomes a matter of identifying the involved tables and SQL statements. Then working back from there based on how those are used by NAV. This is where an understanding of NAV can be very important.

    Suggestion: Create a version of that "Database Locks" page that periodically refreshes based on a timer and writes the information to a log table. Then you can review the log. Disclaimer: I've not look at that page in detail, so I'm not totally sure this is feasible. But worth looking into.





    There are no bugs - only undocumented features.
Sign In or Register to comment.