Skip to content

How to Determine Who Has a Table Locked in Microsoft Dynamics NAV

ArcherPoint’s technical staff pose questions, find answers, and share new discorveries about Microsoft Dynamics NAV

I recently fielded a support call from a frantic customer that went something like this: “Help! Nobody can post anything! All we get is a message that Item Ledger Entry is locked by another user! AAAAAAHHH!!!”

Very useful. The system knows enough to realize there is a block, but won’t bother to tell you which user is causing the problem. The usual fix is to restart the service tier, SQL service, or to reboot the whole SQL server.

Why yes, as a matter of fact, everyone would love to stay an extra 20 minutes late today because of a server reboot. Awesome!

If you’re looking for a better alternative, there are two ways of figuring out who has the lock. You can do so from inside of NAV depending on the version, or you can tell from SQL regardless of the version.

Finding the Guilty Party from Inside NAV

If you are using Version 4.0 through 2009 R2 (it went away in 2013), then you can use Database Sessions. (I did not have a SQL version for 3.x or older handy – it may work in versions older than 4.)

     File Menu, Database, Information; Sessions Tab

Screenshot: View of the Database Sessions Information window

Figure 1 – View of the Database Sessions Information window

Drill down in the Current Sessions field.

Screenshot: Drilldown to the Current Database Sessions field to view all open sessions, including blocking sessions and users

Figure 2 – Drilldown to the Current Database Sessions field to view all open sessions, including blocking sessions and users

The column Blocked will show which user(s) are blocked, and the Blocking User ID will identify the culprit. You can also use the Blocking Connection ID – match that to the Session ID shown in this list, and you can tell which workstation the user is on. This is useful if you have multiple people using shared accounts.

Finding the Guilty Party from SQL Server

Start SQL Server Management Studio (SSMS). Find the database, then right-click and choose New Query.

In the query window, type

     exec sp_who2

Execute that.

Screenshot: Execute the 'exec sp_who2' query from within SQL Server Management Studio (SSMS)

Figure 3 – Execute the “exec sp_who2” query from within SQL Server Management Studio (SSMS)

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.

Now What?

Try not to go all angry-mob-with-pitchforks-and-torches. I’m sure this was not intentional. Try calling the user first so you can find out what they are currently doing, and ask them to exit NAV. Knowing what the person was trying to do can be very useful if this becomes a chronic problem. There can be lots of causes – flaky network connections, code problems because of poor specifications (NAV developers do not make mistakes!), or a workstation crashed and kept the NAV connection open.

If the user is not able to exit NAV, you have two options.

Inside NAV

Find the Session ID that holds the blocking lock

F4 (Delete) on that Session record

This terminates the connection and rolls back the SQL transaction.

From SQL

Once you know the SPID shown in the BlkBy column, you can use this TSQL query command to kill it:

Kill <SPID>

This will also cause a rollback of the locking transaction.

If you found this blog helpful, be sure to check out our other blogs written specifically for NAV developers at



Dear Kyle,
In "Inside NAV" section, you said that "Find the Session ID that holds the blocking lock". How we do that? Session ID is not same with spid, is it?
If we kill spid in blkby with sql query, does it also rollback all the transactions that use a different spid?
Thank in advance.
Best Regards,

Yes, the Session ID shown in the NAV Sessions list is the same as the SQL SPID shown in sp_who2.
At least it is in NAV 2009R2 and older.  Starting with NAV 2013, the Session ID no longer matches the SQL SPID.
If you kill the Blocking SPID, the SQL server will only rollback the Blocking transaction.  The Blocked SPID (and any other SPIDs) should continue their transactions and finish normally.
Read ArcherPoint's Blog Follow us on Twitter Follow us on Facebook Follow us on LinkedIn Link to our RSS feed Join us on Google+ Watch us on YouTube