Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

SQL Server 2008 introduces a new feature for data auditing. The basic operation of this feature is as below:

  1. 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.
  2. When insert happened, the cdc table kept a copy of record.
  3. When update happened, the cdc table kept before changed and after changed records.
  4. When delete happened, the cdc table kept before changed record.
  5. CDC use SQL Server Agent to monitor the log file and write the changes to the cdc table
  6. 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?

share|improve this question
add comment

3 Answers

Well, do you want to keep track of all the versions of the image data, or not? If not, can't you specify only certain columns for CDC instead of just enabling all columns? That will let you keep track of the changes to other columns but ignore the image data changes.

In other words, if all you care about is the current state of the image data, you can get that from the base table. If you care about the previous states of the image data, there isn't any way to store that data without storing it somewhere.

share|improve this answer
add comment

Old records in the capture tables are purged automatically after 2 or 3 days by default, you can configure that time to be shorter if need be; you shouldn't be using the system tables for long-term storage of the data or for reporting, typically an SSIS package would be copying the records out to permanent locations, you could easily skip the records you don't want to persist in the copy process.

share|improve this answer
    
+1 - This is a very good idea –  Lamak Feb 8 '12 at 13:05
add comment

You could just use trigger to track changes in whatever way you wish, you don't have to use the built in CDC. In a past job, we used CodeSmith to generate all our change checking triggers.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.