Steps:
1: before change table with active CDC
EXEC sys.sp_cdc_help_change_data_capture ... to show all active CDC fields
2: after change table with active CDC
Exec sys.sp_cdc_enable_table ... to reactivate table for CDC
3: for CentralDB ... if the replication throwns errors
Check the from job "SN_DocService ExportDocuments" (right click)
Execute reset for for the necessary blocks without attributes
- EXECUTE [CentralDB].[dbo].[CDC_DocPoolResetAccommodation]
- EXECUTE [CentralDB].[dbo].[CDC_DocPoolResetRoom]
- EXECUTE [CentralDB].[dbo].[CDC_DocPoolResetOffer]
--*** Check if CDC (Change Data Capture) is active on a database
USE master
Go
SELECT [name], database_id, is_cdc_enabled FROM sys.databases
Go
--*** Enable CDC on a database
USE MyDatabase
GO
EXEC sys.sp_cdc_enable_db
GO
--*** Disable CDC on a database
USE MyDatabase
GO
EXEC sys.sp_cdc_disable_db
GO
--*** List tables with CDC enabled/disabled
USE MyDatabase
GO
SELECT [name], is_tracked_by_cdc
FROM sys.tables
ORDER BY [name]
GO
--*** Return a list of informations which contains all the required informations for disabling CDC on a table
USE MyDatabase
GO
EXEC sys.sp_cdc_help_change_data_capture
GO
--*** Disabling CDC on a table
USE MyDatabase
GO
Exec sys.sp_cdc_disable_table
@source_schema = N'dbo',
@source_name = N'tblCdbA0',
@capture_instance = N'dbp_tblCdbA0'
GO
--*** Enable CDC on a table
USE MyDatabase
GO
Exec sys.sp_cdc_enable_table
@source_schema = N'dbo',
@source_name = N'tblCdbA0',
@role_name = N'cdc_Admin',
@captured_column_list = N'[A0RID], [G11RID], [A0Pre], [A0Vat], [A0BOn], [A0Alt], [LngID], [S7RID], [A0Sin], [A0MTV]'
GO
--*** additional information
https://www.simple-talk.com/sql/learn-sql-server/introduction-to-change-data-capture-%28cdc%29-in-sql-server-2008/
--*** additional
Note: If a CDC active table will be changed, CDC is no longer active on this table!
Auszug Ergebnis Abarbeitung aus der Job-History (am besten Datum filtern)