As you know Change data Capture (CDC) feature is applied on table level to any database which having CDC enabled. First you have to enable CDC for database then only for table in the database which needs to be tracked by executing sp_cdc_enable_table with some parameter like schema name and table name that you want to track.
If you don’t know how to enable CDC for database then follow the link for more information Change data capture in sql server 2008
Syntax for enabling change data capture for table:
EXEC sys.sp_cdc_enable_table ‘schema_name’,’source_table_name’,’role_name’
But before applying change data capture feature on tables let’s check whether it already been applied or not by executing below query.
SELECT name, is_tracked_by_cdc FROM sys.tables
It will return result that will give you clear idea whether it has been applied or not by looking at is_tracked_by_cdc column. If it gives 1 then CDC is already been enabled for that table.
So as result says you have apply CDC for table let’s say “st_trackingdata”, before applying make sure that sql server agent is running fine. As you run the stored procedure it will create two jobs under sql server agent related to CDC specific to that database.
EXEC sys.sp_cdc_enable_table ‘dbo’,’st_trackingdata’, NULL
If you are using [role_name] as null that means these tracking tables are accessible to everybody.
The Stored Procedure sp_cdc_enable_table enables CDC in sql server 2008. Here we are using only three parameters which are required for this SP. There are several other arguments available as discussed in http://msdn.microsoft.com/en-in/library/bb522475.aspx
After executing the above stored procedure, you can see that two jobs have been created in sql server agent job list with database name. One is cdc.TestDb_capture which calls system procedure sys.sp_MScdc_capture_job to scan the change done in the table. If database is involved in transactional replication this scan will fail.
The second job is cdc.TestDb_cleanup which calls sys.sp_MScdc_cleanup_job to clean up the database changes tables.
As you know, CDC will create new table for capturing change data that will use all columns from source table by default. Column wise data tracking can also be done using column list while configuring CDC with sp_cdc_enable_table.
After successful completion of all above steps, check out the system tables in the database and you will see new table called cdc.dbo_st_trackingdata_CT. It will capture all changes done on dbo.st_trackingdata table.
If you run a select query, you will see that five extra columns have been added in the table as compare to source table.
Here _$start_lsn -> it will give you LSN number which is associated with transaction log. Whenever any transaction was started, it records start of LSN number for that transaction.
_$end_lsn -> It will give you LSN number which is associated with transaction log once transaction has committed successfully.
_$seqval -> It will order the rows changes with in transaction
There are two values which are very important to us is __$operation and __$update_mask.
_$operation -> It indicates the DML operation performed against table, value varies from 1 to 4. Each value indicates different DML operation for sql server table.
- 1 -> stands for Delete operation
- 2 -> stands for Insert operation
- 3 -> stands for old value before update operation
- 4 -> stands for new value after update operation
_$update_mask -> It will show bit value against updated value by any DML operation.