How to Determine Who Has a Table Locked in 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
Figure 1 – View of the Database Sessions Information window
Drill down in the Current Sessions field.
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.
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
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 https://www.archerpoint.com/blog/ap-developer-blog