Troubleshooting CDC enabling failure - Part 2

[SQLPFE]

First published on MSDN on May 30, 2017

In previous blog, I discussed the CDC failure due to guest user disabled in MSDB. My customer also experienced another issue at the same time:

Msg 22832, Level 16, State 1, Procedure sp_cdc_enable_table_internal, Line 622

Could not update the metadata that indicates table [dbo].[Table_Name] is enabled for Change Data Capture. The failure occurred when executing the command 'insert into [cdc].[change_tables]'. The error returned was 515: 'Cannot insert the value NULL into column 'has_drop_pending', table 'LLCProduction.cdc.change_tables'; column does not allow nulls. INSERT fails.'. Use the action and error to determine the cause of the failure and resubmit the request. 

They worked around it by enabling ANSI_NULL_DEFAULT at database level:

ALTER DATABASE DB_Name SET ANSI_NULL_DEFAULT ON;

Then CDC enabled successfully.

The question is what is the root cause for this issue and how we might avoid it in the future?

Let's take a closer look at this issue. The SQL Server version is 2008 SP2, build number is 10.0.4064. When I reviewed other instances that didn't have this issue, this is what I see:

In 'cdc.change_tables' system table, 'has_drop_pending' column is nullable. The error message is complaining about the column does not allow nulls. It seems this table is somehow created using different nullability. There is one connect item for this issue: https://connect.microsoft.com/SQLServer/feedback/details/387050/change-data-capture-sys-sp-cdc-enab... .

Here is how we can reproduce this issue: use SSMS GUI to set this option: In current query window, right click on the query window and select "Query Options…":

Then select "ANSI" in the left pane, uncheck "SET ANSI_NULL_DFLT_ON" option:

Now try to create a test database and enable CDC:

CREATE DATABASE cdcdb
GO

USE cdcdb
GO

CREATE TABLE t1 (
	id INT PRIMARY KEY
	,msg NVARCHAR(20)
	)
GO

EXEC sys.sp_cdc_enable_db
GO

EXEC sys.sp_cdc_enable_table @source_schema = N'dbo'
	,@source_name = N't1'
	,@role_name = N'cdc_Admin';
GO

This gives us the same error on SQL Server 2008 SP4:

Msg 22832, Level 16, State 1, Procedure sp_cdc_enable_table_internal, Line 622

Could not update the metadata that indicates table [dbo].[Table_Name] is enabled for Change Data Capture. The failure occurred when executing the command 'insert into [cdc].[change_tables]'. The error returned was 515: 'Cannot insert the value NULL into column 'has_drop_pending', table 'cdcdb.cdc.change_tables'; column does not allow nulls. INSERT fails.'. Use the action and error to determine the cause of the failure and resubmit the request.

This test showed while creating the "cdc.change_table", SQL Server 2008 uses client side setting to create table and when client side turns off ANSI_NULL_DFLT_ON, the column "has_drop_pending" will not allow null and causes failure during the enablement. The workaround is to enable "ANSI_NULL_DFLT_ON" before enabling CDC.

Further verify whether this is still an issue for other versions of SQL , we repeated the same test – enable CDC with ANSI_NULL_DFLT_ON turned off on client connection. Here are the results:

• SQL Server 2012 SP3: Successful
• SQL Server 2014 SP2: Successful
• SQL Server 2016 SP1: Successful

It appears this issue has been fixed in newer version of SQL Server.

Last thing, to check your current connection setting, we can always use DBCC USEROPTIONS:

Thanks for reading!