cdc work-sheet

Here are the cdc commands I use …


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

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

use SomeDatabaseName
exec sys.sp_cdc_enable_db          -- enable database
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

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

     $operation ... 1=delete
                    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

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

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

--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

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s