How to Join [Active Session] tables with MSSQL SQL DB session

sarmasarma Member Posts: 12
Hi All,

Can some one help me to Join Nav's [Active Session] table info with MSSQL DB level session.

Question:- how can i join below 2 queries

Example:-

Nav session:-

SELECT [timestamp]
,[Server Instance ID]
,[Session ID]
,[User SID]
,[Server Instance Name]
,[Server Computer Name]
,[User ID]
,[Client Type]
,[Client Computer Name]
,[Login Datetime]
,[Database Name]
FROM [dbo].[Active Session]

DB session:-

SELECT s.session_id, s.login_time, s.host_name, s.program_name,
s.login_name, s.nt_user_name, s.is_user_process,
database_id, DB_NAME(s.database_id) AS [database] -- return the ID & database name
FROM sys.dm_exec_sessions s
WHERE s.is_user_process = 1

Regards

Best Answer

Answers

  • sarmasarma Member Posts: 12
    Hi If some one knows the answer; please post it.This info is bit urgent.
  • Slawek_GuzekSlawek_Guzek Member Posts: 1,690
    Hi,

    There is no way to do so as far as I am aware. The NST is using connection pooling, so at any given time SPID visible on the server can map to a different user.

    The way around it is to create as many NST servers as you have users on NAV, each NST service shoud work under separate service account, and single user should only ust its own NST. So, to sum up, a bit like settng manually Classic Client environment :). Then you can have 1:1 mapping between NAV user sessions and SQL sessions (possibly - it still wouldn't tell you which SQL session is NAV background session and which is actual user session)

    Sorry :(

    Slawek
    Slawek Guzek
    Dynamics NAV, MS SQL Server, Wherescape RED;
    PRINCE2 Practitioner - License GR657010572SG
    GDPR Certified Data Protection Officer - PECB License DPCDPO1025070-2018-03
  • sarmasarma Member Posts: 12
    Thanks Slawek,

    While troubleshooting one of Live issues, we found 320+ DB sessions(like DB SessionID:-67) from one NAV server.How can we figure it which NAV screen/session generated this many number of DB sessions.

    Any help would be appreciated.

    Note:-At the time of issue, i have captured the sp_who2 output from MSSQL.

    Regards
  • sarmasarma Member Posts: 12
    Thanks for your help Slawek.
  • sarmasarma Member Posts: 12
    Hi Slawek,

    Quick question, is there way can you inject NAV session ID as a comment one(like ---NAV Session:123), So in SQL we can trace which user generated this call.

    Regards
    Sarma
  • Slawek_GuzekSlawek_Guzek Member Posts: 1,690
    edited 2017-09-28
    No. not really, I don't think so.

    The simplest workaround for building mapping between SQL Server SPID and NAV user/session I can think of is to create a table with user id/session id fields as PK, and one dummy field, create a standalone codeunit with 4 functions each being a handler subscribed to one of codeunit's 1 published events: OnAfterOnGlobalInsert / Modify / Delete / Rename events. In each event handler insert or modify a log record using USERID/SESSIONID pair as PK.

    NAV 2013+ (the NST) is using MARS connection to the server so hopefully all reads and updates would share a single SQL connection / SPID.

    By analyzing updates on this log table you could possibly map SQL SPID to user/session, and later to join that log table to Active Session. Not sure how useful it could be and how often in real situation NST's connection pooling works. I would guess that connections are pretty static, ie a user is mapped to a connection when he/she logs in and then it stays on that connection until he/she logs out. Background sessions might be more dynamic.

    All and all this just my guesswork, it may or may not line up with how it really works.

    Slawek

    Slawek Guzek
    Dynamics NAV, MS SQL Server, Wherescape RED;
    PRINCE2 Practitioner - License GR657010572SG
    GDPR Certified Data Protection Officer - PECB License DPCDPO1025070-2018-03
Sign In or Register to comment.