Is there a way to link Nav 2015 Session ID to SQL SPID?

Hello,

I am wondering if there is a way to link a Nav 2015 Session ID found in Active Session table to the SPID of the session in SQL (found by running the sp_who2 stored procedure for example)

The sp_who2 command will let me identify what session is causing blocking, but it won't say anything about the user as it only shows you the service tier information. I would like to be able to identify the session and the userid/computer name of the blocker when a blocking issue occurs and if I could map SPID to Session ID then this would allow me to do that

Is there a way to do this?

Thanks

Best Answer

Answers

  • kylehardinkylehardin Member Posts: 257
    I have spent a lot of effort trying to solve this problem. I even went so far as to add a SQL trigger to the Active Session table that would attempt to figure out what SQL SPID as each connection is initialized, but it still only returns the SPID of the first NST connection.
    Kyle Hardin - ArcherPoint
  • KevinsanityKevinsanity Member Posts: 8
    Thanks for the effort Kyle, unfortunately not having much effort finding a solution on my end either
  • kaspermoerchkaspermoerch Member Posts: 43
    I don't think that what you want to achieve is possible. A user (Active Session) only has a connection to the NAV Service Tier (NST). Only the NST has a connection to the SQL Server. All SQL Commands will therefor be initiated by the NST and not directly by the user.
  • kylehardinkylehardin Member Posts: 257
    Each user session still becomes a connection from the NST to the SQL server. It should still be possible to figure out which connection belongs to which user, but my attempts have not been successful.
    Kyle Hardin - ArcherPoint
  • bbrownbbrown Member Posts: 3,268
    The NST uses connection pooling. There is not always a 1-to-1 relation between a NAV user connection (to NST) and a SQL Server session.
    There are no bugs - only undocumented features.
  • kylehardinkylehardin Member Posts: 257
    That would explain so much... First, my trigger (the oninsert trigger for the Active Session table) would always give a SPID for the initial NST connection. Second, the SQL connection count was weird. It looks one for 1, but the counts wouldn't match up. All of that having been said, how does SQL Profiler know what to show you if you filter on a single user?
    Kyle Hardin - ArcherPoint
  • einsTeIn.NETeinsTeIn.NET Member Posts: 1,050
    Is there any update on this in the meantime? I mean somehow it is possible. Otherwise NST wouldn't know what SQL session to kill if you send a STOPSESSION request.
    "Money is likewise the greatest chance and the greatest scourge of mankind."
  • bbrownbbrown Member Posts: 3,268
    STOPSESSION does not kill a SQL Session. It only "kills" the NST session.
    There are no bugs - only undocumented features.
  • einsTeIn.NETeinsTeIn.NET Member Posts: 1,050
    Ok, interesting. That would mean that the transaction of the session that was killed still continues to run. If that's the case I wonder how BC is able to resolve dead locks? As far as I can see in Event Log, it terminates one of the sessions to release the dead lock. So, that would require a rollback of the transaction, and thus termination of the SQL session as well. Right?
    "Money is likewise the greatest chance and the greatest scourge of mankind."
  • bbrownbbrown Member Posts: 3,268
    BC doesn't do anything to resolve deadlocks. That is all handled by SQL Server. Yes, it terminates one of the SQL sessions.
    There are no bugs - only undocumented features.
Sign In or Register to comment.