Configuring Change Data Capture (BOL) Understanding the Change Data Capture Timeline Once change data capture is enabled for a database, the capture process begins inserting rows in the change tables associated with defined capture instances. Unless these tables are systematically pruned, over time they will grow to an unmanageable size. Knowing how the change data will be used by other processes is important in developing a strategy to manage the volume of information maintained in change tables. For example, when change data capture is used to provide a systematic stream of change data for another process, such as an ETL application, that is responsible for applying the stream to update another data source, there is little need to retain historical data in change tables. By default, a cleanup process is automatically enabled that retains change data for three days. This retention time is configurable, but should be extended cautiously based upon careful consideration of the disk space needed to accommodate change table growth. When cleanup occurs, the date identified as the low end-point of the change data capture timeline is used to identify an LSN value representing the cut-off point for retained change table entries. All change table entries with LSN values less than the cut-off point are deleted, and the value in the column start_lsn in the table cdc.change_tables is updated for the capture instance to reflect the low cut-off point LSN or current low end point for change data. Because the cleanup strategy is applied to all capture instances, the start_lsn values of the capture instances can coincide over time. This is because, after initialization, this value is set uniformly by the cleanup process. Nevertheless, because tables are enabled at arbitrary points in time, and thus have unique initial values for their capture instance start_lsn value, the low end-point of the change data capture timeline is considered capture instance-specific. As a consequence, the function to retrieve the current minimum LSN value for the change data capture timeline, sys.fn_cdc_get_min_lsn, requires a capture instance name as a parameter. The same is not true, however, for the high end-point of the timeline. All capture instances share the same high end-point, which is specified as the largest commit LSN of any entry currently appearing in a change table defined for the given database. This value can be retrieved using the function sys.fn_cdc_get_max_lsn. When querying for change data, the LSN range specified must lie within the two LSN values identified as the low and high end-points for the change data capture timeline. Cristian Andrei Lefter, SQL Server MVPMCT, MCSA, MCDBA, MCAD, MCSD .NET, MCTS, MCITP - Database Administrator SQL Server 2005http://sqlserver.ro
Configuring Change Data Capture (BOL)
Understanding the Change Data Capture Timeline
Once change data capture is enabled for a database, the capture process begins inserting rows in the change tables associated with defined capture instances. Unless these tables are systematically pruned, over time they will grow to an unmanageable size. Knowing how the change data will be used by other processes is important in developing a strategy to manage the volume of information maintained in change tables. For example, when change data capture is used to provide a systematic stream of change data for another process, such as an ETL application, that is responsible for applying the stream to update another data source, there is little need to retain historical data in change tables.
By default, a cleanup process is automatically enabled that retains change data for three days. This retention time is configurable, but should be extended cautiously based upon careful consideration of the disk space needed to accommodate change table growth. When cleanup occurs, the date identified as the low end-point of the change data capture timeline is used to identify an LSN value representing the cut-off point for retained change table entries. All change table entries with LSN values less than the cut-off point are deleted, and the value in the column start_lsn in the table cdc.change_tables is updated for the capture instance to reflect the low cut-off point LSN or current low end point for change data.
Because the cleanup strategy is applied to all capture instances, the start_lsn values of the capture instances can coincide over time. This is because, after initialization, this value is set uniformly by the cleanup process. Nevertheless, because tables are enabled at arbitrary points in time, and thus have unique initial values for their capture instance start_lsn value, the low end-point of the change data capture timeline is considered capture instance-specific. As a consequence, the function to retrieve the current minimum LSN value for the change data capture timeline, sys.fn_cdc_get_min_lsn, requires a capture instance name as a parameter. The same is not true, however, for the high end-point of the timeline. All capture instances share the same high end-point, which is specified as the largest commit LSN of any entry currently appearing in a change table defined for the given database. This value can be retrieved using the function sys.fn_cdc_get_max_lsn. When querying for change data, the LSN range specified must lie within the two LSN values identified as the low and high end-points for the change data capture timeline.
rsocol:Vezi coloana retention din rezultatul returnat de sp_cdc_help_jobs.Răzvan
ignatandrei: rsocol:Vezi coloana retention din rezultatul returnat de sp_cdc_help_jobs.RăzvanMultumesc Razvan... In ce este exprimat retention ? Il pot schimba ? Daca da, unde/cum ?
retention
bigint
The number of minutes that change rows are to be retained in change tables.
retention is valid only for cleanup jobs.
B_gd_n[ ]Sahlean: ignatandrei: rsocol:Vezi coloana retention din rezultatul returnat de sp_cdc_help_jobs.RăzvanMultumesc Razvan... In ce este exprimat retention ? Il pot schimba ? Daca da, unde/cum ?Conform celor de aici : retention bigint The number of minutes that change rows are to be retained in change tables. retention is valid only for cleanup jobs.