How to match NAV Session ID to SQL SPID

(0) Share

Report

Posted on by Brendan Boelke

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.

Has anyone resolved this issue?

*This post is locked for comments

I have the same question (0)

All responses (9)

Answers (0)

Suggested answer

Alex A 2,913 on at

Like (1)

Report
Hello Brendan,

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:

--KILL [Enter SPID Here] --GO --------------------- USE [Put your Database Name Here] GO EXEC sp_who2 'ACTIVE'

Was this reply helpful? Yes No
Suggested answer

Suresh Kulla 50,245 Super User 2025 Season 2 on at

Like (0)

Report

Brendan,

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.

Was this reply helpful? Yes No
Suggested answer

Roberto Stefanetti 12,998 on at

Like (0)

Report

hi,

look at this post

www.archerpoint.com/.../how-determine-who-has-table-locked-microsoft-dynamics-nav

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

Was this reply helpful? Yes No
Brendan Boelke on at

Like (0)

Report

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.

Was this reply helpful? Yes No
Suggested answer

Suresh Kulla 50,245 Super User 2025 Season 2 on at

Like (0)

Report

Brendan,

As mentioned earlier currently there is no way for you to tie the SPID with the user blocking it, it always show the NAV Service user.

Was this reply helpful? Yes No
Suggested answer

RamG 20 on at

Like (0)

Report

Brendan,

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.

Was this reply helpful? Yes No
Suggested answer

Maneesha 5,891 on at

Like (0)

Report

Dear Brendan Boelke ,

Did you check that what "A Apodaca" said .

I think its working ,,,,,

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Please indicate if it was found and how?

Thank you.

Was this reply helpful? Yes No
Suggested answer

RamG 20 on at

Like (0)

Report

SQL Mantra Tools will log the NAV user names (ie blocker and blocked users) involved in a Blocking, Deadlocks and Slow running query as well as the CAL/AL code involved. The tool also works for Business Central as well. It gives a lot of information to help IT staff and NAV developers to tune any NAV and Business central system. Visit www.sqlmantratools.com for more detail. I will highly recommend the tool for all the NAV and Business Central installations.

Was this reply helpful? Yes No