One of the top questions I am asked by customers when I am on site is what causes AX Orphaned SPIDS and what to do with them and how to identify them. As we know Orphaned SPIDS can cause blocking which lead to end user frustration. They are usually caused by users not exiting the AX application correctly or client crashes or sudden losses of network connectivity. These SIPD can be locking resources and can just sit there waiting for a command from the client that is never coming. These can cause blocking and deadlocking issues and even cause issues with the running of database maintenance plans. Because of this and the frustration is causes I have come up with the workaround below to help deal with little Orphaned Annie and reduce the frustrations she causes. This process uses SQL Server DB Mail, SQL Server Agent, and a stored procedure. SQL Server agent will run a job every 15 minutes that will look for potential orphaned SPIDS, if it finds any it will send off an e-mail to the DBA or whomever with the AOS, AX Session ID, and the SIPD number. The DBA then logs into that particular AOS and looks for that Session ID, if no SPID is assigned then it is an Orphaned SPID and needs to be killed using SQL Server Management Studio and the KILL XXX command where XXX is the SPID number which will get rid of Annie and the trouble she is causing
CREATE PROCEDURE sp_orphanedspids as IF (SELECT COUNT(*) FROM (SELECT C.SESSIONID, C.USERID, C.STATUS, S.AOSID FROM SYSCLIENTSESSIONS C JOIN SYSUSERLOG L ON C.SESSIONID = L.SESSIONID AND C.LOGINDATETIME = L.CREATEDDATETIME JOIN SYSSERVERSESSIONS S ON S.SERVERID = C.SERVERID WHERE C.STATUS IN ('2','3') AND L.TERMINATEDOK = 0 AND L.LOGOUTDATETIME = '1900-01-01 00:00:00.000') AS COUNT) > 0 EXEC msdb.dbo.sp_send_dbmail @profile_name = 'DAX-DBMAIL', --DB MAIL PROFILE @recipients = 'JDOE@CONTOSO.COM', --E-MAIL ADDRESS(ES) SEPERATOR IS ";" @query = 'SELECT C.SESSIONID, C.USERID, C.STATUS, S.AOSID FROM [SYSCLIENTSESSIONS] C JOIN [SYSUSERLOG] L ON C.SESSIONID = L.SESSIONID AND C.LOGINDATETIME = L.CREATEDDATETIME AND C.USERID = L.USERID JOIN [SYSSERVERSESSIONS] S ON S.SERVERID = C.SERVERID WHERE C.STATUS IN (''2'',''3'') AND L.TERMINATEDOK = 0 AND L.LOGOUTDATETIME = ''1900-01-01 00:00:00.000''', @subject = 'DAX - Possible Orphaned SPIDS. See Attached', @attach_query_result_as_file = 1 ;
In the above query i don't have the SIPD number.
How i can retrieve from the session ?
Sorry, it is a typo I need to fix. You need to log into AX with the AOS identified in the e-mail and look for the session ID specified in the online users form and there you can see the SQL SPID. I will correct the BLOG entry.