Here is an interesting support call where Change Data Capture (CDC) log scan job was failing with error below.
Executed as user: domain\svc_account. Log Scan process failed in processing log records. Refer to previous errors in the current session to identify the cause and correct any associated problems. [SQLSTATE 42000] (Error 22859) The statement has been terminated. [SQLSTATE 42000] (Error 3621) Could not open File Control Block (FCB) for invalid file ID 0 in database ‘TEST_DB‘. Verify the file location. Execute DBCC CHECKDB. [SQLSTATE HY000] (Error 5180) The call to sp_MScdc_capture_job by the Capture Job for database ‘TEST_DB’ failed. Look at previous errors for the cause of the failure. [SQLSTATE 42000] (Error 22864). NOTE: The step was retried the requested number of times (10) without succeeding. The step failed.
There was an Application update that dropped index from table cdc.index_columns, one of internal tables used by Change Data Capture (CDC). We recommend avoid “cdc” as user schema to avoid accidental changes to Replication generated objects.
Enabled CDC on a Test database on the same instance of SQL Server. This creates several CDC related tables including cdc.index_columns in the Test database. Scripted out the definition of the index using SSMS and added the index back:
ALTER TABLE [cdc].[index_columns] ADD CONSTRAINT [index_columns_clustered_idx] PRIMARY KEY CLUSTERED
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
Taiyeb Zakir | Microsoft SQL Server Escalation Services