emeadaxsupport: Capturing an AX User’s SQL SPID over multiple AOS servers in AX 2009

[Blog bot]

Источник: http://blogs.msdn.com/b/axsupport/ar...n-ax-2009.aspx
==============

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. This is documented here: http://technet.microsoft.com/en-us/l.../hh699644.aspx

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




Источник: http://blogs.msdn.com/b/axsupport/ar...n-ax-2009.aspx