Identifying and logging database locks

sorenhinrup

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:

SanderDk

Use page 9511 Database Locks.
https://docs.microsoft.com/en-us/dynamics-nav/monitoring-database-locks

sorenhinrup

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.

bbrown

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.