Capturing an AX User’s SQL SPID over multiple AOS servers in AX 2009
Resolving a SQL SPID to an AX user can be useful when trying to diagnose performance or blocking issues on the database server.
As some of you are aware, in AX 2012 it is now possible to track an AX user to a specific SQL SPID.
As you are also probably aware, in AX 2009 you could get the SPIDs from the On-Line users form. However, one restriction of this was that you can only see the SQL SPIDs for the AOS that your client session was connected to. This was fine for small single AOS installations, but wasn’t very useful for larger multiple AOS server configurations.
For this reason, I’ve sometimes given customers the following process to allow these SPIDs to be captured to a log table. This process runs a frequent small batch job on each AOS server, to output the current SPIDs on each AOS to this log table.
You can then query this table, and see which SQL SPID is associated to the each AX User.
If you wish to set this up, please follow the steps below:
1) Save the code at the end of this post to a file called “PrivateProject_LogSPIDSBatchJob.xpo”, and import the XPO into AX.
(You may want to rename the class to suit your normal customisation naming conventions).
2) Create a batch group for each AOS: Administration > Setup > Batch Groups. Called these, e.g. AOS01_SPID
3) Assign the associated AOS server to each batch group. If you don’t see your server listed, go to Administration > Setup > Server Configuration, and make sure that the “Is Batch Server” option is selected.
4) Next go to Basic > Inquiries > Batch Job
5) Create a new Batch Job Record , e.g.
6) Click “View Tasks”, create a new task record for each AOS and assign class name “AAA_SPID_Log_BatchJob” and then for each record assign the unique batch group, e.g. AOS01_SPID, AOS02_SPID
7) Click, Save then close the Batch Tasks form. Back in the Batch Job form, click the “Recurrence” button. Set the reoccurrence to every 1 – 2 mins.
8) Click OK, then back in the Batch Tasks form, click button “Functions” then “Change Status” then set the batch job to Waiting.
9) I also included the class “AAA_SPID_Log_Purge_BatchJob” for purging this. This can also be setup as a normal batch job, to be run just once per day to keep the size of this table small.
Code for XPO:
Exportfile for AOT version 1.0 or later
Formatversion: 1
***Element: CLS
; Microsoft Dynamics AX Class: AAA_SPID_Log_Purge_BatchJob unloaded
; ——————————————————————————–
CLSVERSION 1
CLASS #AAA_SPID_Log_Purge_BatchJob
PROPERTIES
Name #AAA_SPID_Log_Purge_BatchJob
Extends #RunBaseBatch
RunOn #Called from
ENDPROPERTIES
METHODS
Version: 3
SOURCE #canGoBatchJournal
#protected boolean canGoBatchJournal()
#{
# return true;
#}
ENDSOURCE
SOURCE #classDeclaration
#class AAA_SPID_Log_Purge_BatchJob extends RunBaseBatch
#{
#
#///
#/// This source code is freeware and is provided on an “as is” basis without warranties of any kind,
#/// whether express or implied, including without limitation warranties that the code is free of defect,
#/// fit for a particular purpose or non-infringing. The entire risk as to the quality and performance of
#/// the code is with the end user.
#///
#
#}
ENDSOURCE
SOURCE #pack
#public container pack()
#{
#
# return conNull();
#
#}
ENDSOURCE
SOURCE #run
#public void run()
#{
#
# SqlDataDictionary sqlDict;
# ;
# new SqlDataDictionaryPermission(methodStr(SQLDataDictionary, tableTruncate)).assert();
# sqlDict = new SqlDataDictionary();
# sqlDict.tableTruncate(tableName2Id(“AAA_SPID_Log”), false);
# CodeAccessPermission::revertAssert();
#
#}
ENDSOURCE
SOURCE #unpack
#public boolean unpack(container packedClass)
#{
#
# return true;
#
#}
ENDSOURCE
SOURCE #description
#client server public static ClassDescription description()
#{
#
# return (“Purge Log Table for User SPIDS”);
#
#}
ENDSOURCE
SOURCE #main
#static server void main(Args args)
#{
#
# SqlDataDictionary sqlDict;
# ;
# new SqlDataDictionaryPermission(methodStr(SQLDataDictionary, tableTruncate)).assert();
# sqlDict = new SqlDataDictionary();
# sqlDict.tableTruncate(tableName2Id(“AAA_SPID_Log”), false);
# CodeAccessPermission::revertAssert();
#
#}
ENDSOURCE
ENDMETHODS
ENDCLASS
***Element: CLS
; Microsoft Dynamics AX Class: AAA_SPID_Log_BatchJob unloaded
; ——————————————————————————–
CLSVERSION 1
CLASS #AAA_SPID_Log_BatchJob
PROPERTIES
Name #AAA_SPID_Log_BatchJob
Extends #RunBaseBatch
RunOn #Called from
ENDPROPERTIES
METHODS
Version: 3
SOURCE #description
#client server public static ClassDescription description()
#{
#
# return (“Log User SPIDS”);
#
#}
ENDSOURCE
SOURCE #canGoBatchJournal
#protected boolean canGoBatchJournal()
#{
# return true;
#}
ENDSOURCE
SOURCE #classDeclaration
#class AAA_SPID_Log_BatchJob extends RunBaseBatch
#{
#
#///
#/// This source code is freeware and is provided on an “as is” basis without warranties of any kind,
#/// whether express or implied, including without limitation warranties that the code is free of defect,
#/// fit for a particular purpose or non-infringing. The entire risk as to the quality and performance of
#/// the code is with the end user.
#///
#
#}
ENDSOURCE
SOURCE #pack
#public container pack()
#{
#
# return conNull();
#
#}
ENDSOURCE
SOURCE #run
#public void run()
#{
#
# SysClientSessions clientSessions;
# AAA_SPID_Log spidLog;
# xSession sess, serverSess;
# str spids;
# int serverId;
# ;
#
# serverSess = new xSession();
# serverId = serverSess.serverId();
#
# while select clientSessions where clientSessions.Status == 1 && clientSessions.ServerId == serverId
# {
# sess = new xSession(clientSessions.SessionId);
# spidLog.userId = clientSessions.UserId;
# spidLog.SPID = sess.databaseSpid();
# spidLog.insert();
# }
#}
ENDSOURCE
SOURCE #unpack
#public boolean unpack(container packedClass)
#{
#
# return true;
#
#}
ENDSOURCE
SOURCE #main
#static server void main(Args args)
#{
#
# SysClientSessions clientSessions;
# AAA_SPID_Log spidLog;
# xSession sess, serverSess;
# str spids;
# int serverId;
# ;
#
# serverSess = new xSession();
# serverId = serverSess.serverId();
#
# while select clientSessions where clientSessions.Status == 1 && clientSessions.ServerId == serverId
# {
# sess = new xSession(clientSessions.SessionId);
# spidLog.userId = clientSessions.UserId;
# spidLog.SPID = sess.databaseSpid();
# spidLog.insert();
# }
#
#}
ENDSOURCE
ENDMETHODS
ENDCLASS
***Element: DBT
; Microsoft Dynamics AX Table : AAA_SPID_Log unloaded
; ——————————————————————————–
TABLEVERSION 1
TABLE #AAA_SPID_Log
PROPERTIES
Name #AAA_SPID_Log
CreatedDateTime #Yes
ENDPROPERTIES
FIELDS
FIELD #userId
STRING
PROPERTIES
Name #userId
Table #AAA_SPID_Log
ExtendedDataType
ARRAY
#userId
#
ENDARRAY
StringSize #5
ENDPROPERTIES
FIELD #SPID
STRING
PROPERTIES
Name #SPID
Table #AAA_SPID_Log
StringSize #6
ENDPROPERTIES
ENDFIELDS
GROUPS
ENDGROUPS
INDICES
ENDINDICES
REFERENCES
ENDREFERENCES
DELETEACTIONS
ENDDELETEACTIONS
ENDTABLE
***Element: PRN
; Microsoft Dynamics AX Project : LogSPIDSBatchJob unloaded
; ——————————————————————————–
PROJECTVERSION 2
PROJECT #LogSPIDSBatchJob
PRIVATE
PROPERTIES
Name #LogSPIDSBatchJob
ENDPROPERTIES
PROJECTCLASS ProjectNode
BEGINNODE
FILETYPE 0
UTILTYPE 45
UTILOBJECTID 50003
NODETYPE 329
NAME #AAA_SPID_Log_Purge_BatchJob
ENDNODE
BEGINNODE
FILETYPE 0
UTILTYPE 45
UTILOBJECTID 50002
NODETYPE 329
NAME #AAA_SPID_Log_BatchJob
ENDNODE
BEGINNODE
FILETYPE 0
UTILTYPE 44
UTILOBJECTID 50002
NODETYPE 204
NAME #AAA_SPID_Log
ENDNODE
ENDPROJECT
***Element: END
