What is it ?
Change Data
Capture is asynchronous and uses the transaction log in a manner similar to
replication. Past values of data are maintained and are made available in
change tables by a capture process, managed by the SQL Agent, which regularly
scans the T-Log. As with replication, this can prevent re-use of parts of the
log.
Change Data
Capture was introduced with SQL Server 2008+ and requires DataCenter or Enterprise edition. Since SQL Server 2016
SP1 it is available on Standard edition
as well. It has similarities but is different to Change Tracking, a feature
which was also introduced with SQL Server 2008+ (but on all editions).
Compare to Change Tracking
https://technet.microsoft.com/en-us/library/2008.11.sql.aspx
(also excellent in-depth information about both CDC and CT features)
Setup
Change Data Capture
To enable
CDC on the current database:
EXEC sys.sp_cdc_enable_db
To enable
CDC for a specific table(tracking all columns):
@source_schema = N'dbo',
@source_name =
N'Customer',
@role_name =
NULL,
--@filegroup_name = N'MyDB_CT',
@supports_net_changes = 1,
@capture_instance = N'dbo_Customer'
GO
Where:
dbo : Schema name of the
table;
Customer : Table name;
historian : (optional) role name for users
having access to the tracking table;
capture_instance : (optional) instance name. By default
“<Schema>_<Table>”. A table can have up to 2 CDC instances which
are to be used to migrate the CDC table when schema changes were done on the
underlying table.
Retention
period
To
set/change retention period (default is 3 days):
EXEC sp_cdc_change_job
@job_type='cleanup', @retention=7200
Sets the
retention period to 7200 minutes or 5 days. Maximum is 100 years.
To
query/verify the retention period:
SELECT [retention]
FROM [msdb].[dbo].[cdc_jobs]
WHERE [database_id] = DB_ID()
AND [job_type] = 'cleanup'
Managing
the CDC Capture Job
On SQL
Server, a job is scheduled every 5 seconds to read the Transaction Logs and
write the mutations to CDC tables.
The job can
be stopped with:
EXEC dbo.sp_cdc_stop_job
GO
The job can
be started again with:
EXEC dbo.sp_cdc_start_job
GO
Alternatively, the job can be stopped with:
EXEC MSDB.dbo.sp_stop_job
'cdc.<DBName>_capture'
GO
The job can be started again with:
EXEC MSDB.dbo.sp_start_job
'cdc.<DBName>_capture'
GO
Note that
the job is only run when the SQLServerAgent is running.
If the
capture job is not running, the transaction log will keep all changes and stay
growing. Once the capture job is started again, the transaction log will be
handled, all change logs will gradually reach the CDC tables and the
transaction log should shrink again. No change information should be lost as
long as the downtime did not exceed the retention period.
Settings of
the CDC jobs can be viewed with following query:
SELECT * FROM msdb.dbo.cdc_jobs
Managing
the CDC Cleanup job
Together
with the setting up of Change Data Capture, a cleanup job is installed that
will delete CDC records passed the retention period.
Note that this job is scheduled (through SQL
Server Agent) to run only once a day, at 02:00, and that it by default deletes
no more than 5000 rows !
In
databases where more than 5000 rows can be inserted/updated/deleted per day (or
2500 rows updated, as an update counts for 2 CDC records ??), CDC tables will
still keep growing and retention period will not be honored.
Both
retention period (in minutes, by default 3 days = 4320) and threshold (by
default 5000 rows) can be set with the following statement:
EXEC sp_cdc_change_job @job_type='cleanup', @retention = 4320, @threshold = 5000
Settings of
the CDC jobs can be viewed with following query:
SELECT * FROM msdb.dbo.cdc_jobs
The cleanup
job can also be run explicitly:
EXEC sp_MScdc_cleanup_job
Or it’s
schedule in SQL Server Agent could be changed.
Remove
Change Data Capture
To remove
Change Data Capture table for a specific table/instance:
EXEC sys.sp_cdc_disable_table
@source_schema = N'dbo',
@source_name
= N'Customer',
@capture_instance = N'dbo_Customer'
GO
To disable
CDC on the database:
EXEC sys.sp_cdc_disable_db
GO
… to be
continued …
See all
Change Data Capture related stored procedures:
About
Change Data Capture:
Administer
and Monitor Change Data Capture:
No comments:
Post a Comment