CDC Monitoring

Change data capture(CDC) is feature where we can track the changes on table if we enable CDC on that table and CDC should be enabled from DB level first and we can enable it for tables 

As i see so many articles about CDC in the internet i don't  want to go through CDC this topic will discuss only about system tables in CDC and how to do basic monitoring , tracking the errors for CDC enabled tables 

To Monitor the empty scans we can use this select statement and empty_scan_count column in sys.dm_cdc_log_scan_sessions is set to 1 whenever log scan result is empty and it will increment when ever it got empty result from scan

SELECT * from sys.dm_cdc_log_scan_sessions where empty_scan_count <> 0

And for latency between actuall table and CDC we can use below select

SELECT latency FROM sys.dm_cdc_log_scan_sessions WHERE session_id = 0

We can store data from the views   sys.dm_cdc_log_scan_sessions view and the sys.dm_cdc_errors for later analysis and we can later analysis data if there are errors

 

USE msdb; 

DECLARE @schedule_uid uniqueidentifier; 

-- Collect and upload data every 5 minutes  

SELECT @schedule_uid = ( 

SELECT schedule_uid from sysschedules_localserver_view

WHERE name = N'CollectorSchedule_Every_5min') 

DECLARE @collection_set_id int; 

EXEC dbo.sp_syscollector_create_collection_set 

@name = N' CDC Performance Data Collector', 

@schedule_uid = @schedule_uid,

@collection_mode = 0,

@days_until_expiration = 30,

@description = N'This collection set collects CDC metadata', 

@collection_set_id = @collection_set_id output; 

-- Create a collection item using statistics from

-- the change data capture dynamic management view. 

DECLARE @parameters xml; 

DECLARE @collection_item_id int; 

SELECT @parameters = CONVERT(xml,

    N'<TSQLQueryCollector> 

        <Query> 

          <Value>SELECT * FROM sys.dm_cdc_log_scan_sessions</Value> 

          <OutputTable>cdc_log_scan_data</OutputTable> 

        </Query> 

      </TSQLQueryCollector>'); 

EXEC dbo.sp_syscollector_create_collection_item 

@collection_set_id = @collection_set_id, 

@collector_type_uid = N'302E93D1-3424-4BE7-AA8E-84813ECF2419', 

@name = ' CDC Performance Data Collector', 

@frequency = 5,

@parameters = @parameters, 

@collection_item_id = @collection_item_id output;

GO  

Once we create this script we can enable this in SQL Server Management Studio, expand Management, and then expand Data Collection. Right-click CDC Performance Data Collector, and then click Start Data Collection Set and we can use table

Custom_snapshots.cdc_log_scan_data for analysis during issues

I have got this info from MS link and in coming posts i will be posting CDC with Always ON how it will work and what are the issues during the failover  for CDC enabled databases 

https://docs.microsoft.com/en-us/sql/relational-databases/track-changes/administer-and-monitor-change-data-capture-sql-server?view=sql-server-2017