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

Kevinsanity

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

bbrown

The only "user" that SQL Profiler knows about is the service login account used by the NST. It knows nothing of the individual NAV users connected to the NST.

kylehardin

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.

Kevinsanity

Thanks for the effort Kyle, unfortunately not having much effort finding a solution on my end either

kaspermoerch

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.

kylehardin

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.

bbrown

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.

kylehardin

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?

bbrown

The only "user" that SQL Profiler knows about is the service login account used by the NST. It knows nothing of the individual NAV users connected to the NST.

einsTeIn.NET

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.

bbrown

STOPSESSION does not kill a SQL Session. It only "kills" the NST session.

einsTeIn.NET

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?

bbrown

BC doesn't do anything to resolve deadlocks. That is all handled by SQL Server. Yes, it terminates one of the SQL sessions.