Allocating space for CDC data tables and indexes ensures that CDC data tables and indexes have adequate space for current usage and future growth. Type II storage areas are required for all CDC data tables and indexes.
Change tracking table
The _Cdc-Change-Tracking table is the repository of all change capture data in a database. Once Change Data Capture is running, _Cdc-Change-Tracking can grow very quickly and become very large. It is important that you allocate enough space for this table and its indexes.
Change tables and indexes
A change table is created for every source table you define a CDC policy for with a policy level is higher than minimal (0).
Because each source table differs from other source tables in record sizes, number and data types of fields, and growth patterns, you need to make decisions about storage areas for your CDC change tables and indexes.
Consider the following guidelines to create storage areas for individual CDC change tables and their indexes:
For each CDC change table that is expected to be frequently updated, create one storage area for the table.
For CDC change tables that are expected to be less frequently updated and that share similar characteristics (same data type, similar records per blocks or blocks per cluster), create one storage area for each category of tables.
For the indexes of each CDC change table that is expected to be frequently updated, create one storage area for the indexes.
For the indexes of CDC change tables that are expected to be less frequently updated and that share similar characteristics, create one storage area for each category of indexes.
For storage areas of CDC change tables, use:
512 blocks per cluster for high growth
64 blocks per cluster for moderate growth
8 blocks per cluster for low growth
For storage areas containing only indexes associated with CDC change tables, use:
For each CDC change table that is expected to be frequently updated, create one storage area for the table.