Monday, December 30, 2013

Dynamics AX - Archiving Logging Data Per Table

If you are an administrator of Dynamics AX and/or a SQL Server DBA, you probably realize that logging data can build up over time.  With over 400 million such records in our SYSDATABASELOG, I wanted to figure out a way to archive logging data on a per table basis and not just based on a timestamp.  While Microsoft's Intelligent Data Management Framework worked okay for a while, it was quite buggy and didn't provide me with the granular control required by our business.  Therefore I authored my own logging process and recently updated the logic to allow me to archive logging data on a per-table basis.

The first thing you will want to do is create a database in the same instance as your Dynamics AX database.  This database will serve as a temporary repository for the data to be archived.  The database will contain three tables:
  • DATAARCHIVELOG - this will log each iteration of your archive process
  • DONOTARCHIVE - this table will allow you to create custom archival intervals per table
  • SYSDATABASELOG. - this table's schema should be identical to the SYSDATABASELOG in Dynamics AX
Here are the table schemas I've created:



Remember, this is just a temporary repository - having it in the same instance will help with performance, as your permanent archival database should really be on another server to conserve space.

As for the permanent database repository, you only need to script out the schema for the SYSDATABASELOG and create a database with just that table.

While Microsoft recommends only keeping three months of logging data in your production database, the business requirements may override that recommendation.  I personally keep six months of logging data.  There was a recent request to keep additional logging for a particular table and these scripts will give you that ability.

If you have tables that you want to change your retention for, you would do so as follows: find the table ID in AX (correlates to TABLE_ in SYSDATABASELOG) and enter the months (MONTHSTOKEEP) you want to adjust the retention for:


Here is the script to move the data to the temporary repository, as well as make a log entry for the activity:

DECLARE @MAXRECID BIGINT
   ,@RECORDSARCHIVED INT
   ,@RECORDSDELETED INT

INSERT  INTO AX_DB_Archive.dbo.SYSDATABASELOG
        SELECT TOP 10
                SDL.USERNAME
               ,SDL.DESCRIPTION
               ,SDL.LOGTYPE
               ,SDL.DATA
               ,SDL.TABLE_
               ,SDL.LOGRECID
               ,SDL.CREATEDDATETIME
               ,SDL.DEL_CREATEDTIME
               ,SDL.CREATEDBY
               ,SDL.CREATEDTRANSACTIONID
               ,SDL.DATAAREAID
               ,SDL.RECVERSION
               ,SDL.RECID
        FROM    AX_DB.dbo.SYSDATABASELOG SDL
                LEFT OUTER JOIN AX_DB_Archive.dbo.DONOTARCHIVE DNA ON SDL.TABLE_ = DNA.TABLEID
        WHERE   DATAAREAID = '<COMPANY>'
                AND CREATEDDATETIME < ISNULL(DATEADD(month,-MONTHSTOKEEP,GETDATE()),DATEADD(month,-6,GETDATE()))
        ORDER BY RECID

SELECT  @RECORDSARCHIVED = @@ROWCOUNT
       
SELECT  @MAXRECID = MAX(RECID)
FROM    AX_DB_Archive.dbo.SYSDATABASELOG
WHERE   DATAAREAID = '<COMPANY>'

DELETE TOP ( 10 )
        SDL
FROM    AX_DB.dbo.SYSDATABASELOG SDL
        LEFT OUTER JOIN AX_DB_Archive.dbo.DONOTARCHIVE DNA ON SDL.TABLE_ = DNA.TABLEID
WHERE   DATAAREAID = '<COMPANY>'
        AND RECID <= @MAXRECID
        AND CREATEDDATETIME < ISNULL(DATEADD(month,-MONTHSTOKEEP,GETDATE()),DATEADD(month,-6,GETDATE()))

SELECT  @RECORDSDELETED = @@ROWCOUNT 

INSERT  INTO AX_DB_Archive.dbo.DATAARCHIVELOG
        ( TABLENAME
        ,RECORDSARCHIVED
        ,RECORDSDELETED
        ,MAXRECID
        ,DATAAREAID
        )
VALUES  ( 'SYSDATABASELOG'
        ,@RECORDSARCHIVED
        ,@RECORDSDELETED
        ,@MAXRECID
        ,'<COMPANY>'
        )

Here is the script to migrate the data to the permanent repository:

DECLARE @MAXRECID_ARCHIVED BIGINT

INSERT  INTO [LINKED SERVER].AX_DB_Archive.dbo.SYSDATABASELOG
        SELECT TOP 1000
                *
        FROM    AX_DB_Archive.dbo.SYSDATABASELOG
        ORDER BY RECID

SELECT  @MAXRECID_ARCHIVED = MAX(RECID)
FROM    [LINKED SERVER].AX_DB_Archive.dbo.SYSDATABASELOG
                              
DELETE  FROM
FROM    AX_DB_Archive.dbo.SYSDATABASELOG
WHERE   RECID <= @MAXRECID_ARCHIVED