Detect Deadlocks in old NAVs

Roberto Stefanetti

12,998

Like ( 1 )

Report

Detect Deadlocks in old NAVs (<2017)

In old NAV releases (between NAV 2013 and NAV 2016) is very difficult to show and resolve blocking issues.

IN NAV 2017

As a new feature in Dynamics NAV2017, you can configure that information about database deadlocks is written to the Windows Event Log. If database deadlock logging is enabled, Dynamics NAV2017 will log the following:

Dynamics NAV Server instance on which the event occurred.
Dynamics NAV user account that ran the transaction that caused the event.
Dynamics NAV object in C/AL that ran the transaction, such as a page or report.
ID of the object that was run.
C/AL function that ran the transaction that caused the event.
Deadlock report that was received from SQL Server.

https://blogs.msdn.microsoft.com/nav/2016/11/24/database-deadlock-detection-monitoring-in-dynamics-nav-2017/

“HOW TO” FOR OLD NAV RELEASES

In NAV Classic Client

In classic client you could see the active database sessions and blocking information along with the ability to kill sessions if necessary. From NAV 2013 to NAV 2016 is not possible to detect locked By SessionUsers.

Solution #1 – Use SQL Server Monitoring features (SQL Profiler etc.)

Solution #2 – call sp_who2 stored procedure from SQL Command View

Solution #3 – link a SQL View to NAV Table

I will try to explain Solution #2 and Solution #3

Solution #2 : “exec sp_who2”

— SHOW SPID THAT BLOCK

This gives a dump of all current user activity. There is a column called BlkBy, which stands for Blocked By. That gives you the SPID (SQL Process ID) of the person blocking. Find that entry for the BlkBy SPID in this list, and now you know the user.

“HOW TO” TEST – CREATE DEADLOCKS IN NAV

To create deadlock we need two codeunits: example cdu 50002 e 50003 (identical, created by SAVEAS) running in two NAV sessions.

DETECTING DEADLOCKS ON SQL

Call Stored Procedure: exec sp_who2

— SHOW SPID THAT BLOCK

RESULTS – SPID 74 is DEADLOCKED By SPID 80 (in this case the same user login but from another session

Other Nice Scripts (for Command View or from Powershell)

— LOCKING DETAILS

SELECT *

FROM sys.dm_exec_requests

WHERE blocking_session_id <> 0;

— LOCKING DETAILS SUMMARY

SELECT session_id, wait_duration_ms, wait_type, blocking_session_id

FROM sys.dm_os_waiting_tasks

WHERE blocking_session_id <> 0

Solution #3: link a SQL View to NAV Table

THE SOLUTION: USE THE TANDARD SYSTEM SQL VIEW USUFUL TO SHOW LOCKS&DEADLOCKS, YOU CAN USE AS LINKED VIEW IN NAV, TAKE A LOOK BELOW FOR IMPLEMENTATION