Database Dead-lock Detection and Monitoring in Dynamics NAV 2017

Posted on Posted in Microsoft Dynamics NAV 2017, Productivity

Recently, one of our distribution customers experienced dead-lock during posting of Sales order or purchase order. Because of this dead-lock their operation was delayed, one need to wait for another person to complete the posting, if the user try to force through the Posting part by clicking the Post action button, they will receive “Table xxx” is locked by another user. “Dead-lock” affects their sales, as the customer did not want to wait the user to finish posting the sales order.

What “dead-lock” means in Navision? Dead-locks can prevent users from completing tasks in the Dynamics NAV client. A dead-lock occurs when two or more processes or transactions block each other from continuing because each has locked a database resource that the other transaction needs. SQL Server handles dead-locks by terminating and rolling back transactions that started after the first transaction.

In Dynamics NAV 2017, there is a new feature where the system logs this “dead-lock” and captures information related to this dead-lock, such as what is the query or scenario could trigger the block or how long the process happens. To enable the logging, “Enabled Dead-lock Monitoring” field on the Dynamics NAV Administration – database tab has to be “ticked”.

After Enable Dead-lock monitoring has been setup, and the dead-lock happens, you can view the information related to the dead-lock process.

Open event viewer (Windows feature):

Click Application and Services Logs – Microsoft – Dynamics NAV – Server – Admin

Then, filter current log by keying in “705” in the filter box as shown in the image below

Once filtered, you can find the log time when the dead-lock occurs, example as image below.

The red box provides information of the dead-lock process and the paragraph after the timing is the query make the dead-lock process.

If you have many users doing transaction postings or reads & writes at the same time, dead-locks is inevitable. So how do we minimize or mitigate this situation.

  1. Have a faster computer server and faster hard-disk would definitely help as the processing would be faster and the release of the lock from the user would be process faster.
  2. The other frequently overlook item is the type of the hard-disk which translate to the speed of the hard-disk. Talk to your Infra team and determine if the hard-disk is adequately fast enough.

 

  1. Having an Enterprise grade Database system, such as Microsoft SQL DB would greatly help as well, so if possible, upgrade to at least SQL v2012, v2016 or v2017 would be even better.

Regular SQL DB maintenance such as add SQL indexes, reorganize SQL index once a week, rebuild SQL index once a month and add right index will minimize the occurrence of the dead-lock.

26 thoughts on “Database Dead-lock Detection and Monitoring in Dynamics NAV 2017

  1. You ought to take part in a contest for one of the greatest sites on the net.
    I most certainly will highly recommend this blog!

  2. It’s actually a nice and helpful piece of information. I am satisfied that you just shared this helpful info with us. Please stay us informed like this.
    Thank you for sharing.

  3. Heya i am for the first time here. I came across this board and I find It truly useful & it helped me out much. I hope to give something back and aid others like you helped me.

  4. Aw, this was an extremely nice post. Spending some time and actual effort to generate a very good article… but what can I say… I put things off a whole lot and don’t seem to get anything done.

  5. Do you mind if I quote a few of your posts as long as I provide credit and sources back to your blog? My blog is in the very same area of interest as yours and my visitors would genuinely benefit from some of the information you provide here. Please let me know if this alright with you. Regards!

  6. Wow, awesome blog layout! How long have you been blogging for? you make blogging look easy. The overall look of your website is fantastic, let alone the content!

  7. Good day I am so excited I found your site, I really found you by accident, while I was searching on Aol for something else, Regardless I am here now and would just like to say many thanks for a fantastic post and a all round interesting blog (I also love the theme/design), I don’t have time to look over it all at the moment but I have saved it and also added your RSS feeds, so when I have time I will be back to read a lot more, Please do keep up the awesome job.

  8. My family always say that I am wasting my time here at web, except I know I am getting experience all the time by reading such nice posts.

  9. I’ve been exploring for slightly for virtually any good quality articles or weblog posts about this form of area. Exploring in Yahoo I ultimately came across this website. Studying this info So i’m happy to exhibit that I’ve an extremely perfect uncanny feeling I came upon exactly things i needed.
    In a whole lot indisputably can certainly make certain to not forget this website and give it a glance on a continuing basis.

  10. Thanks for the great article. The place else may just anybody get that type of information in such a perfect manner of writing?
    I have a presentation subsequent week, and I am on the search for such info.

  11. Sweet blog! I discovered it while searching on Yahoo News. Do you possess any tips regarding how to get placed in Yahoo News?
    I’ve been trying for a time but I never often arrive! Many thanks

  12. An intriguing discussion is worth comment. There’s no doubt that that you should write more on this issue, it might not be a taboo subject but usually people do not talk about such subjects.
    To the next! Best wishes!!

  13. Does your website have a contact page? I’m having problems locating it but, I’d like to send you an email.
    Either way, great website and I look forward to seeing it improve over time.

  14. I simply could not go away your website before suggesting that I really loved the standard info a person provide for your visitors? Is going to be back incessantly in order to inspect new posts

  15. When someone writes an article he/she retains the image of a user in his/her mind that how a user can be aware of it. Therefore that’s why this paragraph is outstdanding. Thanks!

  16. Simply desire to say your article is as astounding.

    The clearness in your post is simply cool and i could assume you’re an expert on this subject.
    Fine with your permission let me to grab your RSS feed to keep up to date with forthcoming post. Please continue the gratifying work.

  17. Just desire to say your article is as astonishing. The clarity on your submit is simply cool and i
    could assume you’re an expert on this subject. Fine along with your permission let me to seize your
    RSS feed to stay updated with imminent post. Thanks 1,000,000 and please keep up the gratifying work.

  18. I’m amazed, I have to admit. Seldom do I come across a blog that’s both educative and interesting, and without a doubt, you have hit the nail on the head. The problem is something not enough people are speaking intelligently about. Now i’m very happy that I stumbled across this during my search for something regarding this.

  19. you’re in reality a just right webmaster. The site loading speed is incredible.
    It kind of feels that you’re doing any unique trick.
    Also, The contents are masterwork. you’ve done a great process on this topic!

Leave a Reply

Your email address will not be published. Required fields are marked *