Change Data Capture blast radius report

README.md

CDC blast radius analysis

This is a tool I wrote to find the schemas, tables, and columns affected by an unfortunate overbroad update query in SQL Server.

Caveats

1. Apologies for the usage of a cursor and dynamic SQL. If you have a good way to avoid either or both, please share your solution.
2. It is tested on SQL Server 2014, probably works well on later versions, and may be useful on all versions 2008 and later, albeit perhaps with some syntactic adjustments.
3. I have no idea how it handles DDL changes, so be careful if your schema isn't static.
4. I have not yet tested its dynamic SQL bits against SQL injection.

Usage in SSMS or similar connected interfaces

1. USE the database where CDC is enabled and CDC rows are present.

2. Change the first declaration of @lsn in the script to whatever your target starting log sequence number is. This can be found in the __$start_lsn columns of the cdc.<whatever>_CT tables you're interested in.

3. If you want just a single (outermost?) transaction, leave the rest of the query as is. If you want all activity in all CDC-enabled tables within a time window around this transaction, adjust the initial @start and @end declarations as desired, and toggle the comments on these two lines...

   Modify:

   and __$start_lsn = @lsn
   --and __$start_lsn between @start and @end

   to read:

   --and __$start_lsn = @lsn
   and __$start_lsn between @start and @end

4. Uncomment the print statements if you like slightly verbose debugging output.

5. Review and run the query.

A note about performance

On a well-provisioned server with moderate resources, this takes some 11 seconds to report 22 affected columns across 5 affected tables, out of a possible 63 thousand columns among over 2 thousand tables, with 58 of those tables CDC-enabled. CDC cleanup job retention is set to 561600 minutes (390 days), but any high-churn tables would make this very excessive. YMMV.

Wish list & TODOs:

• Improve interface by using options variables instead of the commented lines and hard-coded values; maybe wrap it all in a CREATE PROCEDURE for ease of use
• Add tests, including unit tests, fuzzing, and integration tests that may find environment-specific issues
• Reimplement without any cursors or dynamic SQL

In case you're curious, I did not write or execute the query that caused the damage; I am only the DBA tasked with identifying and resolving the problem, and this audit report is one of several pieces of a larger data deep-dive investigation.

Pull requests and feedback are welcome.

cdc-transaction-audit.sql

declare @res table (sch sysname, tab sysname, col sysname, "count" bigint);

declare c cursor
	LOCAL STATIC READ_ONLY FORWARD_ONLY TYPE_WARNING
for
select sch.name, tab.name, sch_hist.name, tab_hist.name, change_tables.capture_instance, col.column_name
from sys.schemas sch
join sys.tables tab on tab.schema_id = sch.schema_id
join cdc.change_tables on change_tables.source_object_id = tab.object_id
join cdc.captured_columns col on col.object_id = change_tables.object_id
join sys.tables tab_hist on tab_hist.object_id = change_tables.object_id
join sys.schemas sch_hist on sch_hist.schema_id = tab_hist.schema_id
where 1=1
	and tab.is_tracked_by_cdc = 1

declare @sch sysname, @tab sysname, @sch_hist sysname, @tab_hist sysname, @cap sysname, @col sysname;

open c;

fetch next from c
into @sch, @tab, @sch_hist, @tab_hist, @cap, @col;

declare @lsn binary(10) = 0x000026890003A5EC007D; -- chosen by fair dice roll (1d1208925819614629174706176)
declare @time datetime = sys.fn_cdc_map_lsn_to_time(@lsn);
declare @start binary(10) = sys.fn_cdc_map_time_to_lsn('smallest greater than or equal', dateadd(minute, -10, @time));
declare @end binary(10) = sys.fn_cdc_map_time_to_lsn('largest less than or equal', dateadd(minute, 10, @time));

SET NOCOUNT ON;
while @@FETCH_STATUS = 0
BEGIN
	declare @pos int = sys.fn_cdc_get_column_ordinal(@cap, @col);
	declare @sql nvarchar(max);
	declare @ret int;
	set @sql =
N'		set @ret = (
			select count(*)
			from ' + quotename(@sch_hist) + '.' + quotename(@tab_hist) + '
			where 1=1
				and __$start_lsn = @lsn
				--and __$start_lsn between @start and @end
				and sys.fn_cdc_is_bit_set(@pos, __$update_mask) = 1
				and __$operation in (1, 2, 4) -- deleted, inserted, updated
			);';
	--print 'INPUT: [ @col = ' + @sch + '.' + @tab + '.' + @col + ', @pos = ' + convert(varchar, @pos) + ']';
	--print @sql;
	exec sp_executesql @sql,
		N'@lsn binary(10), @start binary(10), @end binary(10), @pos int, @ret int OUTPUT',
		@lsn = @lsn, @start = @start, @end = @end, @pos = @pos, @ret = @ret OUTPUT;
	--print 'OUTPUT: [ @ret = ' + coalesce(convert(varchar, @ret), 'null') + ' ]';
	--print '';

	if @ret > 0
		insert @res (sch, tab, col, "count")
		values (@sch, @tab, @col, @ret);
	
	fetch next from c
	into @sch, @tab, @sch_hist, @tab_hist, @cap, @col;
END
SET NOCOUNT OFF;

close c;
deallocate c;

select sch "schema", tab "table", col "column", sum("count") "total"
from @res
group by grouping sets ((sch, tab, col), ());