SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Change Data Capture

 I started working through some labs for my SQL Server 2008 class and one of the labs was on the new Change Data Capture, essentially an auditing action that allows you to tell what changes have been made to data in a table, including insertions and deletions.  You have to enable the option for each table, but it proves to be very interesting.

It uses LSN from the log, which there are functions to allow you to grab based on times, and so I wondered if it was a view into the log. This makes some sense, but since you backup and truncate the log, your auditing would be compromised. Or your log could grow out of control.

Essentially you set upa retention schedule and this allows you to manage how long you keep data around. I have to dig in more, but this looks like a very interesting feature. What I'm not sure is how well it will be used and if it will become a management headache for administrators.  And you can backup and restore audit data?

The Voice of the DBA

Steve Jones is the editor of SQLServerCentral.com and visits a wide variety of data related topics in his daily editorial. Steve has spent years working as a DBA and general purpose Windows administrator, primarily working with SQL Server since it was ported from Sybase in 1990. You can follow Steve on Twitter at twitter.com/way0utwest


Posted by sush_nmv on 14 January 2009

Is there any way to set up the change tracking tables - the ones that are created with a suffix "-CT" in a separate database?

We have a high transaction environment and the _CT tables can grow to enormously large tables and if they are in the same database, the backup sizes become huge.

Any solution that we can think of?

We can have these tables in a separate file groups, but we take transaction log backups for log shipping and we also need to take full database backups.

Posted by Jody Claggett on 2 February 2009

I am working on a similar situation myself. Although, we have a relatively low volumn of DML hitting our sources tables, we still need to capture more then the default 3 days of data that CDC captures.

I am working on an SSIS solution that will push these the CDC table data from our staging db to our datawarehouse.

Seems to me that since CDC is so new, there are not a lot of solutions out there.

Posted by vinothr 33504 on 19 October 2010

what technology they used prior to CDC in 2005??

Posted by Steve Jones on 19 October 2010

There was no technology prior to 2008 used. You had to manually implement tracking of changes.

Posted by Steve Jones on 19 October 2010

sys.sp_cdc_change_job will help you change the retention schedule (msdn.microsoft.com/.../bb510748.aspx)

Leave a Comment

Please register or log in to leave a comment.