Dynamics F/SCM Customer Experience (CRM) Dynamics GP Dynamics Business Central (NAV) Dynamics SL Power Platform Fabric Copilot

[Jason Dais]

Hello Community,

We frequently encounter an issue with the user’s SQL query continuing to run after the user has disconnected from the Dynamics AX local or RDW client.  The user may choose to disconnect from the client as they are impatient with the spinning wheel or if their session or computer crashes unexpectedly.

We can manually go into SQL and kill the user session but we are wondering if anyone has been able to detect when there is a SQL session with no matching Dynamics client session and automagically end the session.

Thank you,
Jason

ver. Dynamics AX 2012 R2

——————————
Jason Dais
Tolko Industries Ltd.
Vernon BC

——————————

[Alex Meyer]

Jason,

AX client sessions are stored in the SysClientSessions table so you would be looking for a way to tie this table together with a call to something like sp_who2 or select * from master.dbo.sysprocesses and then running the kill command on the SPID.

Unfortunately other than maybe joining on the username I don’t see an obvious relationship relationship between them.

There seems to be a very round about way of doing it by exposing the AX session ID to sysprocesses via modifying a registry path:
http://db-it.dk/wp-content/uploads/2013/04/How-to-Show-Blocking-users-for-Dynamics-AX-2012.pdf

If you could tie the AX session to a SQL session then the process becomes much easier, but this does not seem trivial by any means.

Edit:
Adding more resources about the same idea of tying a AX session to a SQL session:
Finding User Sessions from SPID in Dynamics AX 2012
Map SQL SPID with AX session ID in Dynamics AX 2012
Linking AX Sessions with SQL SPIDs

——————————
Alex Meyer
Director of Dynamics AX/365 for Finance & Operations Development
Fastpath
Des Moines, IA
——————————
——————————————-

[Jason Dais]

Hi Alex,

Thank you very much for the reply.  I checked with our DBA and she confirmed that we already expose the User IDs from the registry and we can easily tie them to the SysClientSessions (AX) table and sysprocesses (SQL).

The next step is to confirm if the session is idle and then set up a job to kill client sessions after ‘n’ minutes of being idle. 

So the next time we have a confirmed ‘idle’ session, we will lookup the AX session status versus the SQL session status. If we can confirm AX is reporting the session as inactive, we know its safe to kill the SQL SPID.  

I will update the post if we are successful.

Thank you again,
Jason

——————————
Jason Dais
Tolko Industries Ltd.
Vernon BC
——————————
——————————————-

[Tony Zeigler]

?Just FYI on this subject:
AX Sessions wait on SQL Connections, not the other way around. Thus, if an AX session which is waiting on a sql connection is killed, the SQL connection won’t even know until it tries to return results to the caller. This is why they keep going after the AX session is killed.

Second note – If the SQL server connection was hung due to blocking, and you kill it, your next attempt may likely get blocked by the same thing. Best thing to do here is check for blocking and identify the root blocker.

Finally – if the SQL server connection was hung due to a large request, and you kill it, it may not clear right away as SQL server will be attempting to roll it back. Roll backs generally take longer than the original transaction, and can cause blocking etc. So if there was no blocking, and it takes a long time to clear after killing it, you’ll want to identify what code caused the large transaction and try to reduce it. Generally in a transactional system like AX, you want small fast transactions, not large ones – or you’ll suffer from a lot of blocking.

——————————
Tony Zeigler
Senior Consultant
Strategic Solutions NW, LLC
Beaverton OR
——————————
——————————————-

[Mark Zerr]

I get the discussion about ax sessions still open after a users forces AX close.  I didn’t catch what this was causing.  If your using RDS and the users forces the session close you may find you need to signout the user.  I find that the user session typical does close it’s a work or business connector that is still open.  This will not stop the user from getting back in.  The real question are those sessions impacting work.   I find users don’t sign out for days(shocking).  You can set up rules with RDS to signout inactive users after x hours.  I have it set to like 8 hours.  I found this helps as the more days a user is logged in the more likely they get to some point they have an issue.  Under kind of related I’m big on clearing auc and kti files.  If you do a search you’ll find articles on this.  You definitely want to do this after code updates but I again feel it’s helpful in keeping users accounts happy.  None of this may be your issue.  Good luck non the less to resolving hanging users sessions.

——————————
Mark Zerr
AX Technical Analyst
New Sunshine LLC
Indianapolis IN
——————————
——————————————-