I was enabling Change Data Capture (CDC) on some tables in a database the other day, and ran into a strange error I hadn’t encountered before.
Here is the basic script I was running:
USE [my_db] GO --Enable CDC on the database EXEC sys.sp_cdc_enable_db GO --Enable CDC on the dbo.Table_1 table EXEC sys.sp_cdc_enable_table @source_schema = N'dbo' ,@source_name = N'Table_1' ,@role_name = NULL ,@supports_net_changes = 1; --Change the retention time to 14 days EXECUTE sys.sp_cdc_change_job @job_type = N'cleanup' ,@retention = 20160; --20160 minutes = 14 days
Here is the error I received when executing the sp_cdc_enable_table procedure:
Msg 22832, Level 16, State 1, Procedure sp_cdc_enable_table_internal, Line 630
Could not update the metadata that indicates table [dbo].[Table_1] is enabled for Change Data Capture. The failure occurred when executing the command ‘sp_cdc_create_change_enumeration_functions’. The error returned was 6517: ‘Failed to create AppDomain “mssqlsystemresource.dbo[runtime].1”.
Exception has been thrown by the target of an invocation.’. Use the action and error to determine the cause of the failure and resubmit the request.
As you can see, this same error was also being written to the Error Log on the server:
After doing some light googling, I came across these two links:
Both links combined seemed to point to 3 different potential solutions:
- Update SQL Server to the latest service pack or Cumulative Update
- There are pending Windows Updates waiting on a machine restart
- The server was renamed recently and requires an update using sp_addserver
The common solution between both links was option #2, so I decided to check with our Windows admin to see about the Windows Updates. Sure enough, some Windows updates had recently been installed and the server was pending a reboot to complete the installation.
Luckily for us, we had already scheduled the server reboot during a maintenance window during the coming weekend, so we only had to wait a couple of days before attempting to run the CDC script again.
After the server was restarted, I was able to run the script to enable CDC on the table with no problems! According to Microsoft:
It’s usually about some inconsistency with the CLR/.NET itself. It means the CLR installation is corrupted (unlikely), or, more likely, a .NET patch was recently installed and the machine is pending a reboot.
Hopefully, this will help someone out there who happens to run across this same problem.