On NAV Techdays 2016 in Antwerpen (Belgium) Microsoft demonstrated a beautiful new feature. Microsoft Dynamics NAV 2017 now offers ‘Database Deadlock Detection and Monitoring’. If NAV 2017 detects a deadlock it will rollback one of the transactions.
If you run the NST under a serviceaccount you need to apply some extra configuration to make this work:
- Alter any event session
- Grand View Server State
Go to the properties of your serviceaccount in SSMS and go to the ‘Securables’ tab:
It’s also possible by executing some T-SQL:
use [master] GRANT ALTER ANY EVENT SESSION TO [CONTOSO\sa_nav] use [master] GRANT VIEW SERVER STATE TO [CONTOSO\sa_nav]
If you don’t do this you will notice errors like this in your application Log:
Server instance: NST2017-RTM
Category: Sql
ClientSessionId: d7ea6dfa-622c-48e1-a6ed-9d5d8402b17c
ClientActivityId: a419d67f-0f56-4394-93cc-b5a1e753344b
ServerSessionUniqueId: 857d43f9-be50-4f2e-abcf-533364da2935
ServerActivityId: 2364cada-9b04-49e4-9268-25260ecdd20f
EventTime: 12/05/2016 16:16:04
Message <ii>Deadlock monitoring feature is enabled but current SQL server user does not have ‘VIEW SERVER STATE’ or ‘ALTER ANY EVENT SESSION’ permissions for the database: NAV2017RTM. Please grant both of those permisions in order to use this feature.</ii>
ProcessId: 4052
Tag: 00000DE
ThreadId: 9
CounterInformation:
By Default DeadLock Monitoring is already enabled on the NAV Service Tier (NST):