Technology

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

Premanath Kumarage · Author
5 min read
A person standing in front of a large wall-mounted analytics dashboard

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.

In this article

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:
SQL
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.

Plain text
KILL <SPID>;

Example:

Plain text
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)

SQL
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)

SQL
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

SQL
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.

Written by

Premanath Kumarage

Author

All insights →

Start here

Every piece here started as a client problem. Tell us yours.

A working session, not a sales call. Bring the process your current system fights you on, and we will tell you plainly what it would take to fix it.

Newsletter

One email a month. No filler.

Stay ahead with Microsoft Business Applications.
Insights, implementation advice and practical ideas to help your business get more from Microsoft technology.

By subscribing, you acknowledge MicroCloud 360’s Privacy Policy.