02 JUN

Change Data Capture in SQL Server

Change data capture, or CDC, is a tool that detects records changes made to a database.  Change Data Capture expose INSERTs, UPDATEs, and DELETEs applied to SQL Server tables.  It Display Historical Records as well current Records.

  • To Detects data changes on Table, First You should enable “Sql Server Agent ” .
pic 1
  • Enable Database for CDC
Run ” EXEC sys.sp_cdc_enable_db” Query , Automatically 6 System Tables generated.
  • Enable a Table for All and Net Changes Queries .
Run ” EXEC sys.sp_cdc_enable_table ” Query . Automatically ” cdc.dbo_Emp_CT ” Table Generated .
  • cdc.captured_columns : By default, It Returns all columns of the source table are captured.
  • cdc.change_tables :  Returns one row for each change table in the database.

cdc.<capture_instance>_CT  :  The table returns one row for each insert and delete operation performed on the source table, And It displays data before updated and data after updated .

cdc.lsn_time_mapping : Returns one row for each transaction having rows in a change table. This table is used to map between log sequence number (LSN) commit values and the time the transaction committed.

  • To Disable Database for Change Data Capture .
-- Disable Database for Change Data Capture . Execute the Stored Procedure.
EXEC sys.sp_cdc_disable_db  ;

Happy Query , Thanks…

Post Author: adama

Related Posts '