SBX - Forum Post Title

How to match NAV Session ID to SQL SPID

Microsoft Dynamics NAV Forum

Question Status

Suggested Answer

NAV 2106 CU2

SQL SERVER 2014 R2

We have a situation where a customer is getting a blocking error on the General Journal Line table. If I enable the Activity Monitor, I see Session ID's that do not match those shown in NAV, and all of which carry the Login information for the user the Application Server is logging in as. The Session list in NAV no longer carries Blocked By or any other information which can help determine who is blocking whom.

I am not looking for a list of all the blocks and how long they last, etc. I am looking to capture a specific scenario where a user is being blocked at a specific moment and I need to know the Username who is blocking them.

You can't really know who is blocking, but there is a nice solution for removing the blocking user, especially when the blocking user is an external report user. Use this query from either Visual Studio or from SQL Management Studio. What you do is run the lower statement when the block is happening. It will tell you the SPID of the user who is blocking in the BlkBy column. Then take that SPID and put it in the upper query and execute. This will resolve the issue:

Since NAV service will be running under a particular NAV user it will always shows that particular user id or sesssion or SPID in the blocking queries, i have not found a way to find who is actually blocking it. Currently i don't think there is a way to find that out.

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

Unfortunately that does not work for 2016 (or 2015, not sure about 2013). The SPID shown by sp_who2 does not match that shown in the NAV Sessions data, and as far as SQL is concerned, every Session is tied to the user executing the NAV Application Server.

We also had the same challenge since NAV2013, where looking from SQL backend point of view all the connection is executed under the login of the service tier user not the NAV user as before. Due to this we have a problem identifying the SQL session which belongs to the NAV user at a particular point in time. I have already raised this to Microsoft and they have recommended to use "SQL Tracing" feature. Once enabled the service tier will send the NAV user name and the NAV C/AL code stack information as comment. Then you need to capture this at real time when the blocking is taking place. We have built a solution based on this to troubleshoot performance problem. You need to be very careful with this as there is a ton of data you will receive. Capturing, storing, analysing this a real challenge. Remember, we like to solve some performance problem, but we should cause more problem than what we intended to solve. Hope this has given some pointers to you.

You can't really know who is blocking, but there is a nice solution for removing the blocking user, especially when the blocking user is an external report user. Use this query from either Visual Studio or from SQL Management Studio. What you do is run the lower statement when the block is happening. It will tell you the SPID of the user who is blocking in the BlkBy column. Then take that SPID and put it in the upper query and execute. This will resolve the issue:

Since NAV service will be running under a particular NAV user it will always shows that particular user id or sesssion or SPID in the blocking queries, i have not found a way to find who is actually blocking it. Currently i don't think there is a way to find that out.

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

We also had the same challenge since NAV2013, where looking from SQL backend point of view all the connection is executed under the login of the service tier user not the NAV user as before. Due to this we have a problem identifying the SQL session which belongs to the NAV user at a particular point in time. I have already raised this to Microsoft and they have recommended to use "SQL Tracing" feature. Once enabled the service tier will send the NAV user name and the NAV C/AL code stack information as comment. Then you need to capture this at real time when the blocking is taking place. We have built a solution based on this to troubleshoot performance problem. You need to be very careful with this as there is a ton of data you will receive. Capturing, storing, analysing this a real challenge. Remember, we like to solve some performance problem, but we should cause more problem than what we intended to solve. Hope this has given some pointers to you.