SQL Server 2008 introduces a new feature for data auditing. The basic operation of this feature is as below:
- When CDC is enabled on a table, SQL Server creates another table (so-called cdc table here) that contains all columns of the original table plus some extra meta-data column.
- When insert happened, the cdc table kept a copy of record.
- When update happened, the cdc table kept before changed and after changed records.
- When delete happened, the cdc table kept before changed record.
- CDC use SQL Server Agent to monitor the log file and write the changes to the cdc table
- Typical size of the meta-data of each record in cdc table is about 51-bytes.
Eg. After 1 insert and 1 update actions The original table kept 1 record The cdc table kept 3 records (1 insert, 2 records per each update)
Assume the image size is 300KB, the total size is about 1,200 KB. It impacts the disk storage and disk IO.
Is it possible NOT to keep cdc record for a type of action (ie. insert)?
Is there any alternative for auditing Image that keeps less copy of records?