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.
“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
WHERE blocking_session_id <> 0;
— LOCKING DETAILS SUMMARY
SELECT session_id, wait_duration_ms, wait_type, blocking_session_id
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
“HOW TO” STEPS
- CREATE 2 SQL VIEWS: V_Deadlocks, V_Deadlocks_
- CREATE NAV LINKED TABLE – Tbl 50003 V_Deadlocks
- CREATE NAV PAGE LOCKED SESSIONS MONITORING
OBJECT ARE LINKED AND ON MY GITHUB PAGE
You are Welcome!