Monitoring SQL Database Deadlocks
You can set up the system to log deadlocks that occur in the SQL database. The deadlocks are recorded in the Windows Event Log of computer running Microsoft Dynamics NAV Server. The log entries provide information about the C/AL code that was run when the deadlock occurred, along with the deadlock report from SQL Server. This information can help you identify and resolve problem areas in the application design.
Deadlocks can prevent users from completing tasks in the Dynamics NAV client. A deadlock 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 deadlocks by terminating and rolling back transactions that were started after the first transaction.
For general information about deadlocks, see Detecting and Ending Deadlocks.
Setting Up Deadlock Logging
Setting up deadlock logging requires you to configure the SQL Server instance and the Microsoft Dynamics NAV Server instance.
Note: If you installed the Microsoft Dynamics NAV Server and database components by using the Install Demo option in the Microsoft Dynamics NAV 2017 Setup, then deadlock logging is set up by default.
Configure the SQL Server instance
To configure the SQL Server instance to log deadlocks, you must assign specific permissions to the database login for the service account that is used on the Microsoft Dynamics NAV Server instance. You can do this using SQL Server Management Studio.
In SQL Server Management Studio, connect to the SQL server instance for Dynamics NAV, and then grant the following permissions:
- On the database level, grant the login the View server state permission.
- On the SQL server instance level, grant the login both Alter any event session and View server state permissions.
For detailed steps on how to do this, see Giving the account necessary database privileges in SQL Server.
The next the a client session is established with the database, a session for monitoring the Dynamics NAV database appears under Management, Extended Events.
Configure the Microsoft Dynamics NAV Server instance
To log deadlocks, you must enable deadlock logging on the Microsoft Dynamics NAV Server instance. You can enable deadlock logging by using the Microsoft Dynamics NAV Server Administration tool or the Set-NAVServerConfiguration cmdlet in the Microsoft Dynamics NAV Administration Shell.
To enable deadlock logging by using the Microsoft Dynamics NAV Server Administration tool, open the server instance for editing, and then select the Enable Deadlock Monitoring check box in the Database section.
For more information about how to use the Microsoft Dynamics NAV Server Administration tool, see Microsoft Dynamics NAV Server Administration Tool.
To enable logging by using the Set-NAVServerConfiguration cmdlet, include the keyname EnableDeadlockMonitoring keyvalue true parameters in the command, such as:
Set-NAVServerConfiguration -ServerInstance DynamicsNAV -KeyName EnableDeadlockMonitoring -KeyValue true
Viewing Deadlocks in the Windows Event Log
Similar to other errors and events in Dynamics NAV, you can monitor deadlocks by using Event Viewer on the computer running Microsoft Dynamics NAV Server. Deadlocks are recorded as warnings in the Microsoft Dynamics NAV Server Admin channel log in the Applications and Services Logs. For general information about how to view the Microsoft Dynamics NAV Server logs, see Monitoring Dynamics NAV Server Events Using Event Viewer.
Deadlock Event Overview
Deadlock event log entries have the event ID 705 and task category 33 (TelemetryData). The following table describes some of important information that is included in deadlock log entry:
|serverInstanceName||Specifies the Microsoft Dynamics NAV Server instance on which the event occurred.|
|user||Specifies the Dynamics NAV user account that ran the transaction that caused the event.|
|AL ObjectType||Specifies the Dynamics NAV object in C/AL that ran the transaction, such as a page or report.|
|AL ObjectNumber||Specifies the ID of the object that was run.|
|AL ScopeName||Specifies the C/AL function that ran the transaction that caused the event.|
|SQL Server deadlock XML report||Includes the deadlock report that was recieved from SQL Server. For more information, see Analyze Deadlocks.|
Note: The system cannot record information about C/AL code that was executed on a different Microsoft Dynamics NAV Server. Therefore, the three attributes AL ObjectType, AL ObjectNumber and AL ScopeName might be empty in a given event log entry.
View a graphical representation of the deadlock event
To view a graphical representation of the deadlock, perform the following steps:
- Open the deadlock event in Event Viewer.
- On the General tab, go to the SQL Server deadlock xml report section, and then copy the text in the deadlock tag (including the start and end tag) to a text editor such as Notepad or Visual Studio Code.
- Save the file as a .xdl type.
- Open the file in SQL Server Management Studio.
Filter on deadlock events
All deadlock events have the trace tag 00000DI. If you only want to see deadlocks events in the log, you can use this tag in an XML path filter on the log, as shown in the following example:
<QueryList> <Query Id="0" Path="Microsoft-DynamicsNAV-Server/Admin"> <Select Path="Microsoft-DynamicsNAV-Server/Admin"> *[EventData[Data[@Name='tag'] and (Data='00000DI')]] </Select> </Query> </QueryList>
For more information about XML filtering, see Advanced XML filtering in the Windows Event Viewer.