How to Find and Understand Locking Sessions in SQL Server (Dynamics NAV)

How to Find and Understand Locking Sessions in SQL Server (Dynamics NAV)

Table locking is a normal but often confusing reality in any multi-user ERP system. In Microsoft Dynamics NAV, locking issues are especially common in manufacturing and service-based organisations, particularly during month-end processing.

At month end, finance teams post large volumes of transactions—such as G/L Entries, Item Ledger Entries, and Value Entries—which increases database activity. When multiple users attempt to update the same tables at the same time, SQL Server may place locks to protect data consistency.

This is when users start asking:

• “Why is my system frozen?”

• “Who is locking the table?”

• “Why can’t I post my transaction?”

This article explains:

1. What locking is (in simple terms)

2. How to find blocking sessions in SQL Server

3. What actions to take

4. Advanced SQL blocking queries

5. Why Dynamics NAV is particularly sensitive to locking

---

What Is Table Locking? (Simple Explanation)

When a user posts a transaction in NAV, SQL Server must ensure that data remains accurate and consistent.
To do this, SQL Server temporarily locks rows, pages, or entire tables while changes are being made.

If another user tries to access the same data at the same time, SQL Server may:

• Make the second user wait

• Or block the second transaction completely

This is called blocking, and it is not a bug—it is a safety mechanism.

---

Step 1: Identify Blocking Sessions Using SQL Server

The fastest way to see blocking activity is through SQL Server Management Studio (SSMS).

Basic Blocking Check

1. Open SSMS

2. Locate your Dynamics NAV database

3. Right-click the database → New Query

4. Run:

EXEC sp_who2;

---

Step 2: Understand the sp_who2 Output

This command shows all active SQL sessions.

The most important column is:

• BlkBy → Blocked By

How to read it:

• If BlkBy is empty, the session is not blocked

• If BlkBy contains a number, that number is the SPID of the blocking session

What this tells you:

• Who is being blocked

• Who is causing the block

Once you locate the SPID shown in the BlkBy column, you now know which session is holding the lock.

---

What Should You Do Next? (Human First)

Before taking technical action:

• Do not assume bad intent

• Locking is almost always accidental

Best practice:

1. Identify the user

2. Call or message them

3. Ask what they were doing

4. Request them to exit NAV normally

This information is extremely valuable if the issue keeps happening.

---

Common Causes of Locking in NAV

• Long-running posting routines

• Network interruptions

• Poorly designed custom code

• Reports running during business hours

• Workstations crashing while NAV is open

• Missing COMMIT statements in C/AL code

---

If the User Cannot Exit NAV

Sometimes the user:

• Is disconnected

• Has a frozen client

• Cannot respond

In these cases, you have two technical options.

---

Option 1: Resolve the Lock from Dynamics NAV (Preferred)

1. Open Session List in NAV

2. Identify the session holding the lock

3. Press F4 (Delete) on the session

What happens:

• The NAV session is terminated

• SQL Server safely rolls back the transaction

• Other users can continue working

✅ This is the safest method when NAV access is available.

---

Option 2: Resolve the Lock from SQL Server (Last Resort)

If NAV access is not possible, you can terminate the SQL session directly.

KILL <SPID>;

Example:

KILL 57;

⚠️ Important notes:

• This forces the connection to close

• SQL Server rolls back the transaction

• Use only when absolutely necessary

---

Advanced SQL Queries for Blocking Analysis

1️⃣ See Who Is Blocking Whom (Detailed)

SELECT
    r.session_id,
    r.blocking_session_id,
    r.status,
    r.wait_type,
    r.wait_time,
    r.wait_resource,
    s.login_name,
    s.host_name,
    s.program_name
FROM sys.dm_exec_requests r
JOIN sys.dm_exec_sessions s
ON r.session_id = s.session_id
WHERE r.blocking_session_id <> 0;

This query shows:

• Blocking chains

• User names

• Client machines

• Programs (NAV client, Job Queue, etc.)

---

2️⃣ See Locked Objects (Tables)

SELECT
    resource_type,
    resource_associated_entity_id,
    request_mode,
    request_status
FROM sys.dm_tran_locks;

Use this to identify:

• Whether locks are at row, page, or table level

• Which objects are locked

---

3️⃣ Find the Exact SQL Statement Causing the Lock

SELECT
    s.session_id,
    t.text
FROM sys.dm_exec_requests r
JOIN sys.dm_exec_sessions s ON r.session_id = s.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE r.blocking_session_id <> 0;

This is extremely useful when diagnosing custom code issues.

---

NAV-Specific Locking Patterns (Very Important)

Dynamics NAV has some unique behaviours that increase locking risk.

1️⃣ Table-Heavy Posting Routines

Posting processes often lock:

• G/L Entry

• Item Ledger Entry

• Value Entry

• Detailed Cust./Vendor Ledger Entry

These tables are high-contention tables, especially during month end.

---

2️⃣ Missing COMMIT Statements

In custom C/AL code:

• Long transactions without COMMIT hold locks longer

• This blocks other users unnecessarily

---

3️⃣ Reports Run During Business Hours

Reports like:

• Adjust Cost – Item Entries

• Post Inventory Cost to G/L

Should never run during peak usage.

---

4️⃣ Job Queue / NAS Locks

Background jobs running via NAS can:

• Compete with users

• Lock tables unexpectedly

• Cause “system-wide slowdowns”

Proper scheduling is critical.

---

5️⃣ Upgrade & Customisation Issues

Older customisations:

• May not follow modern locking best practices

• Often cause blocking after upgrades

---

Best Practices to Reduce Locking

• Schedule heavy jobs outside business hours

• Review custom code for missing COMMITs

• Avoid long-running reports during peak time

• Monitor Job Queue activity

• Educate users about month-end load

---

Summary

Locking in Dynamics NAV is normal—but uncontrolled locking is avoidable.

By:

• Understanding how SQL Server locking works

• Using the right diagnostic tools

• Knowing NAV-specific behaviour

You can resolve issues quickly and prevent them from recurring.