Skip to main content
Delphix

SQL Server with Change Data Capture (CDC) enabled may cause Point-In-Time Refresh to fail

Issue

When refreshing a Microsoft SQL Server VDB using Point in Time recovery, the action may fail with the error message:

Refresh database "EFT". 
Error 
Recovery failed for virtual database "EFT"

Error Code 
exception.db.mssqlvdb.vdb.restore.failed

Suggested Action 
Make sure that the SQL Server instance "YOUR_INSTANCE" on the target host "YOUR_SERVER" is up and that the user "YOUR_USER" has privileges to restore a database on the host

Troubleshooting

This error may be related to the use of the Change Data Capture (CDC) feature of SQL Server.

If this is the case, the Command Output shown by the Action error will mention Change Data Capture, as shown below:

Command Output
...
RESTORE DATABASE [YOUR_DB] WITH NORECOVERY 
RESTORE DATABASE successfully processed 0 pages in 0.448 seconds (0.000 MB/sec). 
Changed database context to 'master'. 
Processed 0 pages for database 'YOUR_DB', file 'YOUR_DB' on file 1. 
Processed 12 pages for database 'YOUR_DB', file 'YOUR_DB_log' on file 1. 
Msg 22841, Level 16, State 1, Server YOUR_SERVER, Procedure sp_cdc_vupgrade, Line 320 
Could not upgrade the metadata for database 'YOUR_DB' that is enabled for Change Data Capture. The failure occurred when executing the action 'alter cdc.change_tables index change_tables_unique_idx with (drop_existing = on)'. The error returned was 4922: 'line 102, state 9, ALTER TABLE ALTER COLUMN column_id failed because one or more objects access this column.'. Use the action and error to determine the cause of the failure and resubmit the request. 

Resolution

Microsoft Knowledge Base article KB2567366 describes a mechanism for skipping the internal "CDC Upgrade" process that triggers this error.

On the target environment experiencing this issue, enable Trace Flag 3101 by executing the following SQL as a user with sysadmin privileges:
DBCC TRACEON (3101, -1)

Once the Refresh completes without error, the feature can be turned off again using the following SQL:

DBCC TRACEOFF (3101, -1)

Updating to the latest available SQL Server Service Pack or Cumulative Update may also resolve this issue.