How do I delete old data from audit tables?

Applies to
ApexSQL Trigger

Summary
This article explains how to delete data in ApexSQL Trigger tables as they grow too large

Description

Why to delete audited data?

ApexSQL Trigger uses three tables to store the audited data: AUDIT_LOG_DATA, AUDIT_LOG_DDL and AUDIT_LOG_TRANSACTIONS. There are a few scenarios in which you may want to delete the old data from these tables:

• When auditing a high-traffic database, auditing tables can grow too large too fast, so you may want to keep the size of the tables under certain extent
• If you are performing daily/weekly review of the audited data, then after generating a Standard, Aggregate or DDL report, old data may become obsolete

ApexSQL Trigger provides two options for deleting the audited data based on criteria you specify – delete data immediately or schedule a job to delete data later

In order to delete the audited data, Delete data stored procedures from the ApexSQL Trigger architecture must be installed

How to delete the data?

1. Audited data can be deleted from the Manage data module. To access it, from the Advanced tab, in the Data group, click the Delete button:

   

2. In the Manage data window, click the New button

   

3. This opens Delete ApexSQL Trigger data wizard

   

   Here you have two options:

   1. Delete all collected data – this option gives a choice between deleting all audit data immediately and scheduling a job to delete all audit data later

   2. Delete data based on criteria you specify – through this option only the audit data that satisfy the deletion criteria will be deleted. Choose between deleting the audit data right away and scheduling a job for later

      

   Criteria for deletion can be:

   • Age: for example, delete data older than one month
   • Log size: for example, delete 30 rows of data

   After setting up the deletion criteria, click Next

4. In the next step choose to:
   1. Run task now – delete data immediately
   2. Create DTS job – provide a name for a job that will be scheduled

   In this example we will select option to Create DTS job

   

   Click Finish button to open Job scheduler

5. From the Job scheduler window you can select how often the deletion job should run (daily, weekly or monthly), the daily frequency and the starting date and/or ending date for the job

   In this example we will schedule the job to run at 9 AM every Monday for the next 3 months

   

   Click OK button to create and schedule the deletion job

The scheduled job will appear in the Manage data window