SQL Server offers two features that monitor changes in a database: Change Data Capture and Change Tracking. These features help applications identify Data Manipulation Language (DML) changes such as insertions, updates, and deletions made to user tables in a database.
Change Data Capture Configuration Settings
? maxtrans* -- Specifies the maximum number of transactions to process per scan cycle.
? maxscans* -- The maximum number of scan cycles to be executed to extract all rows from the log.
? continuous* -- A flag indicating whether the capture job should run continuously (1) or in one-time mode (0). For further details, refer to sys.sp_cdc_add_job (Transact-SQL).*
? pollinginterval* -- Defines the interval in seconds between log scan cycles.
? retention** -- Specifies how long change rows will be stored in the change tables.
? threshold -- The maximum number of deletions allowed in a single cleanup statement.
Enabling Change Data Capture for a Database
USE MyDB GO EXEC sys.sp_cdc_enable_db GO
Disabling Change Data Capture for a Database
USE MyDB GO EXEC sys.sp_cdc_disable_db GO
Enabling 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
Viewing CDC Job Configurations
EXEC sys.sp_cdc_help_jobs
Modifying CDC Job Settings
EXECUTE sys.sp_cdc_change_job @job_type = 'cleanup', @retention = 86400; -- 60 days
Getting Capture_Instance Name
EXEC sys.sp_cdc_help_change_data_capture
Example 1: Reading Changed Columns
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
Example 2: Reading Changed Columns
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 for changes:
SELECT * FROM HumanResources.Shift SELECT * FROM cdc.HumanResources_Shift_CT
Checking If CDC Is Already Enabled for a Database
SELECT [name], database_id, is_cdc_enabled FROM sys.databases
Checking If CDC Is Already Enabled for a Table
SELECT [name], is_tracked_by_cdc FROM sys.tables
CDC System Tables
? cdc.captured_columns - Lists the columns captured.
? cdc.change_tables - Displays all tables enabled for change capture.
? cdc.ddl_history - Records all DDL changes since CDC was enabled.
? cdc.index_columns - Shows the indexes associated with change tables.
? cdc.lsn_time_mapping - Maps LSN numbers.
