How to use CDC (Change Data Capture) in SQL Server

Step 1 -

Check for all Databases that are already Enabled with CDC tracking.

USE master 
GO 
SELECT [name], database_id, is_cdc_enabled  
FROM sys.databases       
GO  

Step 2 -

On above example we can is_cdc_enables all are 0. it means still we are not using.

USE Your_DB
GO 
EXEC sys.sp_cdc_enable_db 
GO 

Database MSO is now 1. Means CDC is enabled for MSO DB.

Step 3 -

Let me check all tables where CDC mode are enabled.

USE MSO 
GO 
SELECT [name], is_tracked_by_cdc  
FROM sys.tables 
GO  

Step 4 -

Enable CDC on MSO Database for Items table

USE MSO 
GO 
EXEC sys.sp_cdc_enable_table 
@source_schema = N'dbo', 
@source_name   = N'items', 
@role_name     = NULL 
GO

Once you will execute above query it will create Two jobs in SQL  Server Agent

Step - 5 

Go to Your Database =>Table=>System Table. Some CDC table you will get.

Step 6 - Update Your table

update Items set ITEMID=451350 where ITEMID=12345

USE MSO 
GO 
SELECT * 
FROM [cdc].[dbo_items_CT]
GO  --this table contains in System Table in Your Particular DB

All changes are recorded into a tracker CDC table.