AXForum  
Вернуться   AXForum > Microsoft Dynamics AX > DAX Blogs
DAX
Забыли пароль?
Зарегистрироваться Правила Справка Пользователи Сообщения за день Поиск Все разделы прочитаны

 
 
Опции темы Поиск в этой теме Опции просмотра
Старый 16.05.2013, 19:11   #1  
Blog bot is offline
Blog bot
Участник
 
25,459 / 846 (79) +++++++
Регистрация: 28.10.2006
emeadaxsupport: Capturing an AX User’s SQL SPID over multiple AOS servers in AX 2009
Источник: 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
__________________
Расскажите о новых и интересных блогах по Microsoft Dynamics, напишите личное сообщение администратору.
 

Похожие темы
Тема Автор Раздел Ответов Посл. сообщение
emeadaxsupport: Multiple database data files, table partitioning using multiple file groups… and Microsoft Dynamics AX 2009 Blog bot DAX Blogs 3 04.10.2011 16:30
emeadaxsupport: Purchase Requisition Workflow in Dynamics AX 2009 fails with Stopped (error): Work item could not be created. Insufficient rights for user Blog bot DAX Blogs 0 18.11.2010 21:12
emeadaxsupport: Debugging non-interactive X++ code in Dynamics AX 2009 when running on Windows Server 2008 Blog bot DAX Blogs 0 23.09.2009 13:05
emeadaxsupport: Unable to install Dynamics AX 2009 Analysis extensions when the default SQL Server Analysis Server collation is set to Turkish_CI_AS or Turkish_CS_AS Blog bot DAX Blogs 0 22.09.2009 04:06
Arijit Basu: AX 2009 - Quick Overview Blog bot DAX Blogs 4 19.05.2008 14:47
Опции темы Поиск в этой теме
Поиск в этой теме:

Расширенный поиск
Опции просмотра

Ваши права в разделе
Вы не можете создавать новые темы
Вы не можете отвечать в темах
Вы не можете прикреплять вложения
Вы не можете редактировать свои сообщения

BB коды Вкл.
Смайлы Вкл.
[IMG] код Вкл.
HTML код Выкл.
Быстрый переход

Рейтинг@Mail.ru
Часовой пояс GMT +3, время: 04:14.
Powered by vBulletin® v3.8.5. Перевод: zCarot
Контактная информация, Реклама.