SQL Server provides two features that track changes to data in a database: change data capture and change tracking. These features enable applications to determine the DML changes (insert, update, and delete operations) that were made to user tables in a database.
Change Data Capture configuration settings
● maxtrans* — The maximum number of transactions to process in each scan cycle.
● maxscans* — The maximum number of scan cycles to execute in order to extract all rows from the log.
● continuous* — A flag indicating whether the capture job is to run continuously (1), or run in one-time mode (0). For more information, see sys.sp_cdc_add_job (Transact-SQL).*
● pollinginterval* — The number of seconds between log scan cycles.
● retention** — The number of minutes that change rows are to be retained in change tables.
● threshold — The maximum number of delete entries that can be deleted using a single statement on cleanup
Enable Change Data Capture for a Database
USE MyDB GO EXEC sys.sp_cdc_enable_db GO
Disable Change Data Capture for a Database
USE MyDB GO EXEC sys.sp_cdc_disable_db GO
Enable Change Data Capture for a 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 GO
View CDC Job Configurations
EXEC sys.sp_cdc_help_jobs
Change CDC Job Settings
EXECUTE sys.sp_cdc_change_job @job_type = 'cleanup', @retention = 86400; --60 days
Get Capture_Instance Name
EXEC sys.sp_cdc_help_change_data_capture
Reading changed columns Example 1
USE AdventureWorks2014
DECLARE @from_lsn binary (10), @to_lsn binary (10)
SET @from_lsn = sys.fn_cdc_get_min_lsn('HumanResources_Shift')
SET @to_lsn = sys.fn_cdc_get_max_lsn()
SELECT * FROM cdc.fn_cdc_get_all_changes_HumanResources_Shift(@from_lsn, @to_lsn, 'all')
ORDER BY __$seqval
Reading changed columns Example 2
USE AdventureWorks2014
DECLARE @from_lsn binary (10), @to_lsn binary (10)
SET @from_lsn = sys.fn_cdc_get_min_lsn('HumanResources_Shift')
SET @to_lsn = sys.fn_cdc_get_max_lsn()
SELECT * FROM cdc.fn_cdc_get_all_changes_HumanResources_Shift(@from_lsn, @to_lsn, 'all')
ORDER BY __$seqval
Check changes
SELECT * FROM HumanResources.Shift SELECT * FROM cdc.HumanResources_Shift_CT
Verify whether CDC is already enabled for database
SELECT [name], database_id, is_cdc_enabled FROM sys.databases
Verify whether CDC is already enabled for a table
SELECT [name], is_tracked_by_cdc FROM sys.tables
CDC System Tables
● cdc.captured_columns This table returns the result for the list of the captured columns.
● cdc.change_tables This table returns a list of all the tables which are enabled for capture.
● cdc.ddl_history This table contains a history of all the DDL changes since capture data enabled.
● cdc.index_columns This table contains indexes associated with the change table.
● cdc.lsn_time_mapping This table maps LSN number
