How to reduce the size of an audit log

support
1

I have audit log turned on db table, where big number of data changes occurs, but I need to trace these changes, so I cannot simply turn auditing off. But database size grows rapidly. Is there some way to solve this problem?

2

As of now the audit log stores all information about a new record when the record is created. This way it is possible to track even changes that occur directly in a database. The original record as it was saved first is stored in the log no matter what changes were done manually in the database.

But this information is very space consuming and sometimes not that important.

So one way how to reduce the size of the audit log is to delete the “new record” logs, only keeping the changes and deletions.

Warning: Backup your database before proceeding!

You can use the following script on your DATA database. This script uses a technique of copying out data to a temporary database. It is by way the fastest method because just running DELETE statement on millions of records can sometimes take many hours.

-- create a temporary database because typically the space
-- reduction takes place in a MS SQL Express database where 
-- there is a 10 GB limit
CREATE DATABASE log_cleanup
-- move all but INSERT logs to the temp db
SELECT * INTO log_cleanup.dbo.AsapDataAuditLog 
  FROM AsapDataAuditLog WHERE ActionType <> 4
-- clean the log quickly
TRUNCATE TABLE AsapDataAuditLog
-- update the current log from old MEMO data type to 
-- NVARCHAR(MAX) which takes less space
ALTER TABLE AsapDataAuditLog ALTER COLUMN OldValue NVARCHAR(MAX) NULL
ALTER TABLE AsapDataAuditLog ALTER COLUMN NewValue NVARCHAR(MAX) NULL
-- move log records back to the database
INSERT INTO AsapDataAuditLog (
   ActionType, Id, NewValue, NewValueId, OldValue, OldValueId, RecordCreated, 
   RecordCreatedBy, RecordUpdated, RecordUpdatedBy, refColumnId, 
   refParentRecordEntityId, refParentRecordId)
  SELECT ActionType, Id, NewValue, NewValueId, OldValue, OldValueId, 
   RecordCreated, RecordCreatedBy, RecordUpdated, RecordUpdatedBy, 
   refColumnId, refParentRecordEntityId, refParentRecordId 
  FROM log_cleanup.dbo.AsapDataAuditLog
-- here you can actually drop the cleanup db but in case something
-- goes wrong it is good to drop it manually.
-- DROP DATABASE log_cleanup

See https://origam.com/doc/display/architect/Data+Audit+Log to learn more about how audit log data are stored.

2 Likes