Dynamics AX in the Field

Microsoft Dynamics AX from the Premier Field Engineering team at Microsoft.

Dynamics AX and little Orphaned SPID Annie

Dynamics AX and little Orphaned SPID Annie

Rate This
  • Comments 2

 

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

  • Enable DBMAIL on Dynamics AX Production SQL Server Instance
    • For more on setting up Database Mail see

                        http://technet.microsoft.com/en-us/library/ms175951(v=SQL.105).aspx

  • Create a SQL Server DB Mail Profile
    • You can use an internal mail account or you can even use a Hotmail account all you need is an e-mail address and the SMTP server address
    • When testing this I just created a new Hotmail account and the Hotmail SMTP server address is smtp.live.com

  
 

  • Build Stored Procedure listed below
    • You need to input the DB-Mail profile you just created and add the e-mail address of the people whom you want notified when a possible orphaned SPID is found
      before you build the Stored Procedure.  Build this procedure in your production Dynamics AX database.
    • Create SQL Agent Job that runs every 15 minutes that runs the Stored Procedure sp_orphanedspids
    • When you receive an e-mail with the AOS name, AX Session ID, and SQL SPID look up the specified session id on the specified AOS.  If it is an orphaned session then KILL the associated SPID in SQL Server Management Studio with KILL XXX command where XXX is the SPID number
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 ;


 

  • Hi Michael

    In the above query i don't have the SIPD number.

    How i can retrieve from the session ?

    Kind Regards

    Denis

  • 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.

Page 1 of 1 (2 items)