Applies to
ApexSQL Audit
Summary
This article explains what to do when the ApexSQL Audit central repository database, where ApexSQL Audit stores all audit information, grows too quickly.
Symptoms
The ApexSQLCrd database on the central ApexSQL Audit instance is growing fast. Full database backups are becoming bigger every day. Checking the MDF and LDF file size will show a significant increase in one or both of these files.
Cause
As new audited records are constantly being added to the ApexSQLCrd database, the MDF file will be growing as long as ApexSQL Audit auditing is running. This is expected behavior.
The LDF file growth may be attributed to the fact that the ApexSQLCrd database is in the Full recovery model by default and transaction log backups may not be created regularly, or frequently enough.
When a database is in the Full recovery model, transactions are kept in LDF file until it is backed up, after which, existing space in the log will be marked for re-use may be overwritten by new transactions. When there is no more re-usable space available in the log, a transaction log backup needs to be created to truncate the log and prevent it from increasing in size.
Resolution
Central repository database growth depends on the environment – in high transaction databases and busy environments with many audited SQL Server instances and databases, it will grow faster than in environments with a smaller number of audited databases and fewer transactions.
If you think that the ApexSQLCrd MDF file is growing too quickly:
-
- Archive the central repository database.
- Check ApexSQL Audit configuration settings in GUI and determine whether it is necessary to audit everything which has been selected.
- In the Operations section, deselect DDL if you don’t have to audit DDL operations on SQL Server – CREATE, ALTER and DROP statements executed on SQL Server objects – databases, logins, and roles.
- In the Operations section, deselect Security if you don’t have to audit GRANT, DENY, and REVOKE statements executed on SQL Server logins and roles.
- In Databases, select only those databases you need to audit – do not add all databases.
- In Operations, deselect the operation types you don’t have to audit:
- DDL (Create, Drop, Alter, Truncate statements)
- DML (Insert, Update, Delete, Merge, Lock table statements)
- Query (Select statements)
- Execute statements
- Errors reported by database engine
- Warnings
- Security (login, user, permissions operations)
- In the Objects section, select include objects and go through the list and select only those objects you want/need to audit. By default, all objects are selected.
- In the notification that appears above the settings section click Apply
-
- Check the ApexSQLCrd database recovery model. You can do that in SQL Server Management Studio, by opening the Database properties dialog, Options tab. By default, it’s set to Full
- If the Recovery model is Full, there are two options:
- Switch the database to the Simple recovery model Keep in mind that this will break the full chain of transaction logs, reduce the chances for successful recovery and restoring to a point in time will not be possible.
- Start creating transaction log backups on regular basis. It would be best to create a scheduled maintenance task. If you’ve already been creating transaction log backups, increase the frequency at which they occur.
The less objects you choose to audit, the slower ApexSQLCrd will grow.
Note: Alternatively, the Advanced filter can be used to specify filter settings
The deselected objects and operation types will not be audited anymore and ApexSQLCrd will not grow as fast as before.
To keep the LDF file size from growing:
