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?
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.