7 Replies Latest reply on May 15, 2018 8:14 AM by Roger Palmen

    AF Audit Trail cleanup

    Roger Palmen

      Hi All,

       

      As i did not find anything in the documentation, let's pick our community brain for ideas.

      We use the AF Audit Trail feature to capture changes on the AF database and that of course consumes more space over time. Is there any way to prune this data after a specific period of time? E.g. all records > 1 year?

       

      I'm not really knowledgeable on the CDC functionality used in SQLserver to make this work, so also don't really understand the SQLserver side of things to work on that side. The documentation on SQLserver hints on supporting this pruning, so curious if that can be used in some way.

        • Re: AF Audit Trail cleanup
          Roger Palmen

          One of the other questions i have is the huge size of two CDC tables:

          • cdc.dbo_AFElementAttributeValue27_CT has 5M6 records consuming almost 22Gb. What are the possible causes of that? We cannot have had that many changes on the values/ We don't (or should not) have Analysis writing back to Attibutes. We do have a lot of Ad-Hoc Analysis (Analysis DataReference), but my assumption is that those should not cause this # of records. The dbo.AFElementAttributeValue table is just 2765 records summing to 2.7Mb, which is a ratio (on records) of 2000:1. Does that mean i have 2000 changes captured for every attribute???
          • cdc.dbo_AFEventFrameAttributeValue27-CT has 31M records consuming almost 19Gb. The underlyhing table dbo_AFEventFrameAttributeValue has 7M5 records for 2Gb. That is a ratio of 3:1 for the changes, meaning data base been recaptured at least 3 times on average?
            • Re: AF Audit Trail cleanup
              scheung

              You might want to take stock of the change records broken down by attributes like __$operation or rid.

               

              The __$operation would give you insight on what types of changes are being made. For example, a lot of updates would mean a lot of records with __$operation 3 (old value) and 4 (new value).

               

              The rid would give you insight on if changes are being made to the same attribute values over and over.

               

              Given the size of the tables, I suspect there is one workflow that regularly updates element attribute values and another workflow which occasionally updates event frame attribute values.

               

              If you need additional assistance, I suggest reaching out to our Technical Support team.

                • Re: AF Audit Trail cleanup
                  Roger Palmen

                  Hi Sam,

                  Thanks for the excellent info! Really helpful, you showed me the smoking gun. We indeed have an application that updates EventFrame attributes regularly, and that causes the bulk of the Audit Trail records. I will look into pruning specific sets of data for this AF database that we don't need to Audit in the first place.

                  • Re: AF Audit Trail cleanup
                    Roger Palmen

                    I noticed another application that causes a lot of records, and that is the Integrator for Business Analytics, which keeps a DataCache in AF. Just a quick analysis shows about 50% to 75% of the Audit Trail records are caused by entries for the Integrator in the configuration database.

                     

                    As that is a real issue, i will create a techsupport ticket for that...

                • Re: AF Audit Trail cleanup
                  scheung

                  There are 2 SQL Agent jobs to service the audit trail feature, a:

                   

                  1. Capture job (e.g. cdc.PIFD_capture)
                  2. Cleanup job (e.g. cdc.PIFD_cleanup)

                   

                  The cleanup job can help you prune your change tables. You'll want to:

                   

                  1. Configure the cleanup job (e.g. set the desired retention period)
                  2. Enable the cleanup job

                   

                  The cleanup job can be configured by executing sys.sp_cdc_change_job. The following example changes PIFD's CDC cleanup job to have a 1 year retention period:

                   

                  --configure cleanup job retention for 1 year (525600 minutes)
                  USE PIFD;
                  EXEC sys.sp_cdc_change_job @job_type = N'cleanup', @retention=525600;
                  

                   

                  To confirm the settings for the job, we can use sys.sp_cdc_help_jobs:

                   

                  --confirm settings
                  USE PIFD;
                  EXEC sys.sp_cdc_help_jobs;
                  

                   

                  Enabling the cleanup job can be done with SSMS or with TSQL (dbo.sp_update_job). In SSMS:

                   

                  1. Expand SQL Server Agent
                  2. Expand Jobs
                  3. Right-click on the cleanup job and click Enable

                   

                  Further customizations of the cleanup job might be editing its threshold value (by default it deletes in chunks of 5000) or changing its schedule (by default it runs nightly at 2am).