Configuring Database Deadlock Detection and Monitoring

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:

alter-any-event-session

view-server-state

nav-deadlock-monitoring-configuration-sql-server

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):

enable-deadlock-monitoring

Advertisements

Author: Ibrahiem Rasoelbaks

I'm a Technical Consultant working at 4PS.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s