Change Tracking and Change Data Capture

SQL Server 2008 introduced 2 features that allow tracking changes to table data, namely Change Tracking and Change Data Capture.

Change Tracking is enabled on database level using the statement


and on table level using


To query the server which databases are actually tracking-enabled, use the following query

SELECT * FROM [sys].[change_tracking_databases]

The list of tracking-enabled tables can be retrieved by this statement

SELECT * FROM [sys].[change_tracking_tables]

Change Data Capture is enabled using the stored procedure sys.sp_cdc_enable_on:

EXEC sys.sp_cdc_enable_db

If your database editition does not support CDC, you will receive the error:

Msg 22988, Level 16, State 1, Procedure sp_cdc_enable_db, Line 12
This instance of SQL Server is the [***] Edition. Change data capture is only available in the Enterprise, Developer, and Enterprise Evaluation editions.

CDC adds the cdc schema and other database objects

When a database is enabled for change data capture, the cdc schema, cdc user, metadata tables, and other system objects are created for the database.

so the easiest method to detect whether CDC is enabled is to check for the [cdc] schema.

A table can be enabled for CDC using the SP sys.sp_cdc_enabled_table:

EXEC sys.sp_cdc_enable_table
@source_schema = N'dbo',
@source_name   = N'MyTable',
@role_name     = N'MyRole',
@filegroup_name = N'MyDB_CT',
@supports_net_changes = 1

The parameter @role_name must be provided, but can be NULL. The following parameters are optional.

To find out which tables are CDC-enabled, use the SQL statement

SELECT * FROM [cdc].[change_tables]

One Response to Change Tracking and Change Data Capture

  1. […] of the new Change Tracking features found in SQL Server 2008 and higher, SMO only supports scripting Change Tracking, but does not […]

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: