SBX - Search With Button

SBX - Forum Post Title

How to match NAV Session ID to SQL SPID

Microsoft Dynamics NAV Forum

Brendan Boelke asked a question on 21 Oct 2016 3:12 PM
My Badges

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.

Has anyone resolved this issue? 

Reply
Alex A responded on 21 Oct 2016 3:27 PM
My Badges
Suggested Answer

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'
Reply
Suresh Kulla responded on 21 Oct 2016 3:49 PM
Suggested Answer

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.

Reply
Roberto Stefanetti responded on 22 Oct 2016 1:43 AM
My Badges
Suggested Answer

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

Reply
Brendan Boelke responded on 25 Oct 2016 9:44 AM
My Badges

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.

Reply
Suresh Kulla responded on 25 Oct 2016 9:53 AM
Suggested Answer

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.

Reply
Rama Mathanmohan responded on 23 Jan 2017 4:40 AM
Suggested Answer

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.

Reply
Maneesha De Silva responded on 24 Jan 2017 9:38 AM
My Badges
Suggested Answer

Dear Brendan Boelke ,

Did you check that  what "A Apodaca" said .

I think its working ,,,,,

Reply
Gafuentes responded on 20 Jun 2017 3:42 PM

Please indicate if it was found and how?

Thank you.

Reply
Alex A responded on 21 Oct 2016 3:27 PM
My Badges
Suggested Answer

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'
Reply
Suresh Kulla responded on 21 Oct 2016 3:49 PM
Suggested Answer

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.

Reply
Roberto Stefanetti responded on 22 Oct 2016 1:43 AM
My Badges
Suggested Answer

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

Reply
Suresh Kulla responded on 25 Oct 2016 9:53 AM
Suggested Answer

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.

Reply
Rama Mathanmohan responded on 23 Jan 2017 4:40 AM
Suggested Answer

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.

Reply
Maneesha De Silva responded on 24 Jan 2017 9:38 AM
My Badges
Suggested Answer

Dear Brendan Boelke ,

Did you check that  what "A Apodaca" said .

I think its working ,,,,,

Reply

SBX - Two Col Forum

SBX - Migrated JS