cdc work-sheet

Here are the cdc commands I use …

--STATUS

--is cdc enabled on my current database?
select is_cdc_enabled, * from master.sys.databases

--is cdc enabled on this table?
use SomeDatabaseName
go
select is_tracked_by_cdc, * from sys.tables order by 1 desc

-- ENABLE

use SomeDatabaseName
go
exec sys.sp_cdc_enable_db          -- enable database
go
exec sys.sp_cdc_enable_table       -- enable table
   @source_schema = N'dbo',
   @source_name = N'SomeTableName' -- table to be monitored
   @role_name = NULL,              -- login used to restrict access
   @filegroup_name = N'cdc_fg'     -- used to set path for change-table to its own drive
go

-- MISC QUERIES

select * from cdc.dbo_SomeTableName_CT -- changes plus 5x cdc columns
where __$start_lsn = 0xSomeLsnNumber

     $operation ... 1=delete
                    2=insert
                    3=value before update
                    4=value after update
     
     $update_mask ... bit-map of column-names updated (IE: 3 & 4 only)

select * from cdc.captured_columns -- list of logged column-names
select * from cdc.change_tables    -- table(s) monitored by cdc
select * from cdc.ddl_history      -- ddl chages
select * from cdc.index_columns    -- indexes on monitored tables
select * from lsn_time_mapping     -- lsn numbers with time-stamps

select min(tran_begin_time), max(tran_begin_time)
from cdc.lsn_time_mapping          -- check its running

--REMOVE

use Somedatabase
go
exec sys.sp_cdc_help_change_date_capture -- get values for @capture_instance etc
go

use SomeDatabase
go
-- exec sys.sp_cdc_disable_table  -- disable table
@source_schema = N'dbo',
@source_name = N'SomeTableName',      -- table name
@capture_instance = N'dbo_SomeTableName'
go
use somedatabase
go
--exec sys.sp_cdc_disable_db      -- disable database ... and delete all data and objects
go

--MANAGEMENT

--show latency in seconds & hours
select latency, latency/3600 latency_hrs, *
from SomeDatabase.sys.dm_cdc_log_scan_sessions

--show current sqljob settings
select * from msdb.dbo.cdc_jobs -- note: 4320 mins = 3 days

--chanage 'capture' job settings
exec sys.sp_cdc_change_job @job_type = 'capture',
  @maxtrans = 600,       --500 default
  @maxscans = 200,       --10
  @continuous = 1,       --1
  @pollinginterval = 2   --5
--dont forget to stop/start job to use these new settings

-- see if cdc (aka replication) is stopping the log-file shrinking
select name, log_reuse_wait_desc from sys.databases

-- see percentage log-file used
dbcc sqlperf(logspace)

-- show cdc error-log
select * from master.sys.dm_cdc_errors

-- emergency dump all pending transactions, allowing shrink
--sp_repldone @xactid = null, @xact_segno = null, @numtrans = 0, @reset = 1