Example of Change Data Capture in sql server 2008 1

Lets understand this feature with help of some examples, here we will create test database with dummy table and data to perform DML operation like INSERT, UPDATE and DELETE.

create database TestDb

use testdb

create table st_trackingdata

(st_name varchar(10),

Grade char(1) )

Lets add one dummy data in table with out enabling CDC (Change data capture) feature for database.

insert into st_trackingdata values(‘sandeep’,’B’)

If you are using any existing database and want to confirm whether CDC is enabled for that database or not, you can make use of below query to find the status.

select name, is_cdc_enabled from sys.databases where name = ‘testdb’

Lets enable CDC for TestDB database for demo

USE TestDb

EXEC sys.sp_cdc_enable_db

If you don’t want to check whether  CDC is enable on the database or not then also if execute above statement it will check internally and give you the message below.

Database ‘TestDb’ is already enabled for Change Data Capture. Ensure that the correct database context is set, and retry the operation. To report on the databases enabled for Change Data Capture, query the is_cdc_enabled column in the sys.databases catalog view.

Now if you run the above query to verify the database, you see that  is_cdc_enabled column has been updated by 1 that means we have enabled Change data capture feature for testdb database.

Similarly we have to enable CDC on table level to capture the changes done by user. Execute the below query to enable CDC for specific table like st_trackingdata.

EXEC sys.sp_cdc_enable_table @source_schema = N’dbo’, @source_name   = N’St_trackingdata’, @role_name     = NULL

enable cdc for tables in sql server 2008

After executing above stored procedure, it will create cdc.dbo_st_trackingdata_ct table in system table under “testdb” database. Also it will create two jobs in sql server, one is called as cdc_testdb_capture and second is cdc.testdb_cleanup.

Insert Operation for table

Lets add one record in st_trackingdata table to check whether these changes are getting reflected in the auditing table or not that is cdc.dbo_st_trackingdata_ct.


insert into st_trackingdata values(‘Sonu’,’A’)

Checking whether records has been updated in the tracking table or not by using below query.

select * from cdc.dbo_st_trackingdata_ct

change data capture in sql server 2008

You can see one entry in the tracking table that means new data has been added in the source table. The _$opartion column value indicates insert operation.

Update Operation

Now we have two records in the source table(st_trackingdata), lets perform operation to update first records and see how changes will capture in auditing table (i.e. cdc.dbo_st_trackingdata_ct).

update dbo.st_trackingdata

set st_name = ‘Sanjay’ where Grade = ‘B’

Records has been updated in source table, will check with auditing table by executing below query

select * from cdc.dbo_st_trackingdata_ct

As result says, we got two more entries in the auditing table after update operation. Here if you see the numbers in _$operation column that indicates which operation has been performed on the source table. You can clearly see that old value and new value both are updated in the table.

Tracking changes data in sql server 2008

Delete Operation

Lets perform delete operation on source table and check with tracking table, what data has been deleted. If you run select query on tracking table (cdc.dbo_st_trackingdata_ct) then you will below output.

sql server 2008 new feature change data capture

Here one more entry has been added in the tracking table that indicates delete operation and deleted values at the end.

Important Observation

After completing all DML operation, going through the additional columns of tracking table that are generated after enabling CDC for table.  Lets understand what is _$update_mask ?

_$update_mask represents hexadecimal value in sql server. To understand better we find to convert into binary format. As you know _$update_mask column indicates updated values by any DML operation.

In our example, we are using st_trackingdata table which is having 2 column st_name and grade.

When we executed insert operation against source table, updated 2 columns st_name and grade that’s why you will see hexadecimal value (0x03) that means binary value (11) for two columns.

Now when we executed update statement against source table, updated only 1 column that is st_name that’s why it showing hexadecimal value (0x01) which mean binary value (1) and same is the case for delete operation which remove two column values so hexadecimal value is (0x03).


How to disable Change data capture feature in sql server 2008 ?

Disabling Change data capture feature is very simple execute the below query which will disable and drop CDC related objects that are created earlier.

EXEC sys.sp_cdc_disable_db

As you know, CDC is enable at table and database so the ideal way to disable CDC in the reverse order first at table level then database level. If you execute below query it will give you the

list of objects involved in CDC.

EXEC sys.sp_cdc_help_change_data_capture

Now execute below procedure to disable the CDC from table level.

EXECUTE sys.sp_cdc_disable_table @source_schema = N’DBO’,    @source_name = N’st_trackingdata’,

@capture_instance = N’dbo_st_trackingdata’;

Now you can see tracking table has been dropped after executing above stored procedure.


One comment on “Example of Change Data Capture in sql server 2008

  1. Pingback: How to enable Change Data Capture on one or more Tables ← SqlServer-query

Leave a Reply




+ 8 = 11