Skip to content

Latest commit

 

History

History
170 lines (126 loc) · 12.2 KB

monitor-database-deadlocks.md

File metadata and controls

170 lines (126 loc) · 12.2 KB
Raw
title description ms.custom ms.date ms.reviewer ms.service ms.topic author
Monitoring SQL Database Deadlocks
Read about how you can configure Business Central on-premises to log deadlocks that occur in the SQL database.
bap-template
04/01/2021
jswymer
dynamics-365-op
conceptual
SusanneWindfeldPedersen

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 [!INCLUDEserver]. The log entries provide information about the 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.

About Deadlocks

Deadlocks can prevent users from completing tasks in the [!INCLUDEprod_short] 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 [!INCLUDEserver] instance.

Note: If you installed the [!INCLUDEserver] and database components by using the Install Demo option in the [!INCLUDEnav_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 [!INCLUDEserver] instance. You can do this using SQL Server Management Studio.

In SQL Server Management Studio, connect to the SQL server instance for [!INCLUDEprod_short], and then grant the following permissions:

  • On the database level, grant the login the View database 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 time a client session is established with the database, a session for monitoring the [!INCLUDEprod_short] database appears under Management, Extended Events.

Configure where to store deadlock events

By default, SQL Server uses an in-memory data structure called a ring_buffer target to store deadlock events. When the [!INCLUDEserver] is notified about the deadlock, it reads data from the target ring_buffer target. You have the option to also store the events to a file on the SQL Server, called an event_file target, and configure the [!INCLUDEserver] to read from this file instead of the ring_buffer target. An important difference between the ring_buffer target and event_file target is that the ring_buffer target has a storage size limitation of 5MB, while the event_file target provides a much greater storage capacity. Using the event_file target can eliminate potential overloads in high volume situations. So, if your setup has a high volume of database traffic, you might have to change the SQL Server to write deadlock events to an event_file target as described the steps that follow. If you want to use the default ring_buffer target, then no further action is required.

Note

Reading from the event_file target is only supported in [!INCLUDEprod_short] Cumulative Update 3 and later.

  1. Modify the deadlock monitoring session to use a file-based target (known as an event_file target).

    The event_file target writes event session output from a buffer to a disk file that you specify. There are two ways to do this:

    • From Object Explorer, open the session's Properties, and then on the Data Storage page, add an event_file type target.
    • Using a query, run the ALTER EVENT SESSION transact-sql statement. For example: 
      ALTER EVENT SESSION [Demo Database BC_deadlock_monitor]
    ON SERVER
      ADD Target package0.event_file
    (
      SET filename=N'C:\logging\mydeadlocks.xel',max_file_size=(10240)
    )

    Replace C:\logging\mydeadlocks.xel with the path and file name that you want to store the data.

    For more information see Alter an Extended Events Session and Targets for Extended Events in SQL Server.

  2. In the [!INCLUDEprod_short] database, create a view that has the name deadlock_report_event_file_view and uses the new event_file target.

    For example: 
    USE [Demo Database BC]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE VIEW deadlock_report_event_file_view AS
    SELECT event_data AS event_raw_data
    FROM sys.fn_xe_file_target_read_file ('C:\logging\*.xel', null, null, null)
GO

    Replace C:\logging\ with the path to the event file.

Note

You can delete the ring_buffer target if you like. However, this is not required.

Configure the [!INCLUDEserver] instance

To log deadlocks, you must enable deadlock logging on the [!INCLUDEserver] instance. You can enable deadlock logging by using the [!INCLUDEadmintool] or the Set-NAVServerConfiguration cmdlet in the [!INCLUDEadminshell].

  • To enable deadlock logging by using the [!INCLUDEadmintool], 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 [!INCLUDEadmintool], see Business Central 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 MyServerInstance -KeyName EnableDeadlockMonitoring -KeyValue true

    For more information about how to use the [!INCLUDEadminshell], see Business Central PowerShell Cmdlets and Set-NAVServerConfiguration Cmdlet.

Viewing Deadlocks in the Windows Event Log

Similar to other errors and events in [!INCLUDEprod_short], you can monitor deadlocks by using Event Viewer on the computer running [!INCLUDEserver]. Deadlocks are recorded as warnings in the [!INCLUDEserver] Admin channel log in the Applications and Services Logs. For general information about how to view the [!INCLUDEserver] logs, see Monitor Business Central 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:

Information Description
serverInstanceName Specifies the [!INCLUDEserver] instance on which the event occurred.
user Specifies the [!INCLUDEprod_short] user account that ran the transaction that caused the event.
AL ObjectType Specifies the [!INCLUDEprod_short] object in 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 AL function that ran the transaction that caused the event.
SQL Server deadlock XML report Includes the deadlock report that was received from SQL Server. For more information, see Analyze Deadlocks.

Note: The system cannot record information about AL code that was executed on a different [!INCLUDEserver]. 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:

  1. Open the deadlock event in Event Viewer.
  2. 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.
  3. Save the file as a .xdl type.
  4. 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.

Monitor using Application Insights

If you're deployment is enabled for it, you can also view data about deadlocks in an Application Insights resource in Azure. For more information, see:

See Also

Monitoring Business Central Server Events
Monitoring SQL Database Locks
Monitoring Business Central Server
Use SQL Server Management Studio