CDC Log Scan fails with Could not open File Control Block

Here is an interesting support call where Change Data Capture (CDC) log scan job was failing with error below.

Error

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.

Cause

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.

Resolution

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
(
[object_id] ASC,
[index_ordinal] ASC,
[column_id] ASC
)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]
GO

Taiyeb Zakir | Microsoft SQL Server Escalation Services