Skip to main content
Delphix

MSSQL Server with change data capture (CDC) enabled may cause point-in-time refresh to fail

Issue

When refreshing the MS SQL Server vDB using Point-In_Time may cause the recovery to fail with the following Action error message (this error can be intermittent): 

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 is related to a feature on SQL Server called Change Data Capture (CDC) and the issue can be identified by reviewing the tail of the Action error.  at in the  affects SQL 2014 was an RTM release.

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

On your target environment with the issue, please run the following as a sysadmin user (as per https://support.microsoft.com/en-au/kb/2567366):

DBCC TRACEON (3101, -1)


This will cause SQL Server to skip parts of the CDC upgrade process.

Test the Refresh and when completed the testing you can turn it back off with:

DBCC TRACEOFF (3101, -1)

 

Once we patched it with the latest SQL server 2014 service pack the refresh process worked without having the sql instance running the trace flag

Additional Information

For more details, please read this Microsoft KB: https://support.microsoft.com/en-au/kb/2567366