CDC (Change Data Capture) was introduced in SQL2008 Enterprise only, only available to Standard edtions since SQL2016 SP1. It’s not available in Express edtion as Express edtion doesn’t have SQL Agent.
CDC involves three-tier configuration: Server-level (agent job), Database-level and Table-level. It works in all recovery modes. When using CDC, we need to minotr log file growth and adjust retention period, especially if not using FULL recovery mode.
CDC is like a video recording of every single change, where as Change Tracking (CT) is more like a “motion sensor”. CT tells you which rows changed and what the latest values are, but id doesn’t keep a history of every intermediate step. CT is much more lightweight than CDC.
Prereqquisites
To be able to configure CDC at database level, you need to be a memter of sysadmin role.
Also, to be able to configure CDC, the database owner must be set to sa. If not, we may receive following error:
Could not update the metadata that indicates database < your database> is enabled for Change Data Capture. The failure occurred when executing the command ‘SetCDCTracked(Value = 1)’. The error returned was 15517: ‘Cannot execute as the database principal because the principal “dbo” does not exist, this type of principal cannot be impersonated, or you do not have permission.’. Use the action and error to determine the cause of the failure and resubmit the request.
To change database owner:
ALTER AUTHORIZATION ON DATABASE::<YOUR DATABASE> TO sa;
To enable CDC at database level
This will create the cdc schema, system tables and metadata needed for tracking.
USE yourDatabase
GO
EXEC sys.sp_cdc_enable_db;
-- verity it's enabled
SELECT name,is_cdc_enabled, suser_sname(owner_sid)
FROM sys.databases
WHERE name = 'db name'
To enable CDC at table level
EXEC sys.sp_cdc_enable_table
@source_schema = N'dbo',
@source_name = N'table name',
@role_name = NULL, -- Set to a specific role to restrict who can see changes
@supports_net_changes = 1; -- Required if you want to query "final" row states
-- Verify table is tracked
SELECT name, is_tracked_by_cdc
FROM sys.tables
WHERE name = 'table name';
Once first table is enabled cdc, SQL Server automatically creates two jobs in the SQL Server Agent:
- cdc.YourDatabaseName_capture: Scans the log and moves data to change tables.
- cdc.YourDatabaseName_cleanup: Deletes data older than the retention period (default 4320 mins / 3 days).
To change retention period for CDC
-- Change retention to 1 day (1440 mins)
EXEC sys.sp_cdc_change_job
@job_type = N'cleanup',
@retention = 1440;
After change the setting, SQL job requires to be restarted for the changes to take effect
EXEC sys.sp_cdc_stop_job @job_type = 'cleanup';
EXEC sys.sp_cdc_start_job @job_type = 'cleanup';
To change CDC capture job setting
-- View current job settings
EXEC sys.sp_cdc_help_jobs;
-- Increase performance if log is growing too fast
EXEC sys.sp_cdc_change_job
@job_type = N'capture',
@maxtrans = 1000, -- Increase transactions per cycle. Default 500.
@maxscans = 20; -- Increase scans per cycle. Default 10.
GO
After change the setting, SQL job requires to be restarted for the changes to take effect
EXEC sys.sp_cdc_stop_job @job_type = 'capture';
EXEC sys.sp_cdc_start_job @job_type = 'capture';
To monitor CDC job fail
-- Create an Operator (the person/group who gets the email)
USE msdb;
GO
EXEC dbo.sp_add_operator
@name = N'DBA_Alerts',
@enabled = 1,
@email_address = N'your-email@company.com';
GO
Configure SQL job failure notification to email operator.
Sometimes the job doesn’t “fail” but gets stuck or has errors. We can create a system-wide alert for CDC-specific errors (Error numbers 22848-22855 are common for CDC).
EXEC msdb.dbo.sp_add_alert
@name = N'CDC Capture Error',
@message_id = 0,
@severity = 16, -- Severity 16 is a standard user error
@event_description_keyword = N'CDC',
@database_name = N'YourDatabaseName';
-- Link the alert to your Operator
EXEC msdb.dbo.sp_add_notification
@alert_name = N'CDC Capture Error',
@operator_name = N'DBA_Alerts',
@notification_method = 1; -- 1 = Email
To check for CDC errors
SELECT * FROM sys.dm_cdc_errors;
To check performance/latency
This shows how far “behind” the capture job is from the real-time log.
SELECT
latency AS [Latency_in_Seconds],
last_commit_time AS [Last_Processed_Log_Time],
empty_scan_count
FROM sys.dm_cdc_log_scan_sessions;
To query all the changes
We should use the built-in functions rather than querying the cdc.dbo_YourTable_CT tables directly.
-- Get the LSN (Log Sequence Number) range
DECLARE @from_lsn binary(10) = sys.fn_cdc_get_min_lsn('dbo_YourTableName'); --capture instance is in the format of schemaName_tableName
DECLARE @to_lsn binary(10) = sys.fn_cdc_get_max_lsn();
-- Query all changes in that range
SELECT * FROM cdc.fn_cdc_get_all_changes_dbo_YourTableName(@from_lsn, @to_lsn, N'all');
Note, if @from_lsn/@to_lsn is invalid (0x00000000000000000000, for example the capture instance name is spelled wrong) or NULL, the function cdc.fn_cdc_get_all_changes_… might return error “An insufficient number of arguments were supplied for the procedure or function cdc.fn_cdc_get_all_changes_ … .”
We can also filter changes by a specific time range instead of using the LSN, with the help of sys.fn_cdc_map_time_to_lsn function. This function converts human-readable dates into the LSN format that CDC function requires. Please note, if the @begin_time is older than the CDC cleanup retention period (default 3 days), sys.fn_cdc_map_time_to_lsn will return NULL. This will cause the “insufficient arguments” error for cdc.fn_cdc_get_all_changes_… function.
DECLARE @begin_time datetime, @end_time datetime, @from_lsn binary(10), @to_lsn binary(10);
-- 1. Set your time window
SET @begin_time = '2023-10-01 08:00:00';
SET @end_time = '2023-10-01 17:00:00';
-- 2. Map times to LSNs
-- 'smallest greater than' ensures you get changes starting at or just after the begin time
SET @from_lsn = sys.fn_cdc_map_time_to_lsn('smallest greater than', @begin_time);
-- 'largest less than or equal' gets changes up to that point
SET @to_lsn = sys.fn_cdc_map_time_to_lsn('largest less than or equal', @end_time);
-- 3. Run the query (wrapped in a NULL check to prevent the "insufficient arguments" error)
IF @from_lsn IS NOT NULL AND @to_lsn IS NOT NULL
BEGIN
SELECT * FROM [cdc].[fn_cdc_get_all_changes_dbo_tblLIBAltCode](@from_lsn, @to_lsn, N'all');
END
ELSE
BEGIN
PRINT 'No LSNs found for the specified time range.';
END
The operation (__$operation) codes:
- 1: Delete
- 2: Insert
- 3: Before Update
- 4: After Update
To query the net changes
To see only the final state of each row within a specific window, we need to call the cdc.fn_get_net_changes_… function instead of the cdc.fn_get_all_changes_… function.
DECLARE @start_time datetime = '2025-12-01 00:00:00'; -- Adjust as needed
DECLARE @end_time datetime = GETDATE();
DECLARE @from_lsn binary(10), @to_lsn binary(10);
DECLARE @capture_instance nvarchar(100) = 'dbo_tblLIBAltCode';
-- 1. Get the absolute minimum LSN available in the CDC logs
DECLARE @min_lsn_available binary(10) = sys.fn_cdc_get_min_lsn(@capture_instance);
-- 2. Map start time to LSN
-- If the time is too old (returning NULL), we fall back to the minimum available LSN
SET @from_lsn = ISNULL(
sys.fn_cdc_map_time_to_lsn('smallest greater than', @start_time),
@min_lsn_available
);
-- 3. Map end time to LSN
SET @to_lsn = sys.fn_cdc_map_time_to_lsn('largest less than or equal', @end_time);
-- 4. Safety Check: Ensure the requested range is valid
-- @from_lsn must be >= @min_lsn_available and <= @to_lsn
IF (@from_lsn IS NOT NULL AND @to_lsn IS NOT NULL AND @from_lsn <= @to_lsn)
BEGIN
SELECT * FROM [cdc].[fn_cdc_get_net_changes_dbo_tblLIBAltCode](@from_lsn, @to_lsn, N'all');
END
ELSE
BEGIN
SELECT 'No changes found' AS Status,
@from_lsn AS StartLSN,
@to_lsn AS EndLSN,
@min_lsn_available AS MinAvailable;
END
To be able to query net change, the table must have a PK or Unique index at the time CDC was enabled to support net change. Also, @supports_net_changes must have been set to 1 when first enabled CDC on the table.
In a net changes result, the operations are simplified:
- 1: Delete
- 2: Insert
- 4: Update (The final state of the row after all updates)
Using the Net Changes function (fn_cdc_get_net_changes_…), SQL Server is smart enough to handle the lifecycle for you:
- If a row is inserted and then deleted in the same window, it returns nothing.
- If a row is updated 10 times and then deleted, it returns only the Delete (1).
- If a row is inserted and then updated, it returns only the Insert (2) with the latest values.
To query for change on specific column(s)
To see changes on specific columns, we utilise function sys.fn_cdc_is_bit_set and sys.fn_cdc_get_column_ordinal.
DECLARE @from_lsn binary(10) = sys.fn_cdc_get_min_lsn('dbo_tblLIBAltCode');
DECLARE @to_lsn binary(10) = sys.fn_cdc_get_max_lsn();
-- 1. Get ordinals for all columns in your "Watch List"
DECLARE @Ord_AltCode int = sys.fn_cdc_get_column_ordinal('dbo_tblLIBAltCode', 'AltCode');
DECLARE @Ord_Description int = sys.fn_cdc_get_column_ordinal('dbo_tblLIBAltCode', 'Description');
DECLARE @Ord_Status int = sys.fn_cdc_get_column_ordinal('dbo_tblLIBAltCode', 'Status');
-- 2. Query with a filter that checks if ANY of those bits are set
SELECT
[__$operation],
[AltCode],
[Description],
[Status],
[__$update_mask]
FROM [cdc].[fn_cdc_get_all_changes_dbo_tblLIBAltCode](@from_lsn, @to_lsn, N'all')
WHERE
-- Always include Delete (Operation 1) if you want deleted records too
__$operation = 1 OR
-- Always include Inserts (Operation 2) if you want new records too
__$operation = 2 OR
-- Update on specific columns
(__$operation = 4 AND (
-- Only return rows where one of our target columns changed
sys.fn_cdc_is_bit_set(@Ord_AltCode, __$update_mask) = 1 OR
sys.fn_cdc_is_bit_set(@Ord_Description, __$update_mask) = 1 OR
sys.fn_cdc_is_bit_set(@Ord_Status, __$update_mask) = 1
)
);
To manually clean up after sync
We can use sys.sp_cdc_cleanup_change_table to manually cleanup instead of waiting for the set retention period. To run this SP, you must be a member of the db_owner role.
-- 1. Use the LSN you just successfully finished syncing
DECLARE @processed_lsn binary(10) = @to_lsn;
-- 2. Manually trigger cleanup for this specific capture instance
EXEC sys.sp_cdc_cleanup_change_table
@capture_instance = 'dbo_tblLIBAltCode',
@low_water_mark = @processed_lsn;