Устранение ошибок включения CDC — Часть 2

Автор: Brass Contributor, Troubleshooting CDC enabling failure - Part 2

В предыдущей статье блога я рассказывал о сбое CDC из-за отключённого пользователя guest в MSDB. Тогда же мой заказчик столкнулся и с другой проблемой:

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.

Они обошли её, включив параметр ANSI_NULL_DEFAULT на уровне базы данных:

ALTER DATABASE DB_Name SET ANSI_NULL_DEFAULT ON;

После этого CDC включился успешно.

Вопрос в том, какова первопричина этой проблемы и как мы можем избежать её в будущем?

Давайте рассмотрим эту проблему подробнее. Версия SQL Server — 2008 SP2, номер сборки — 10.0.4064. Когда я изучил другие экземпляры, на которых этой проблемы не было, я увидел следующее:

В системной таблице cdc.change_tables столбец has_drop_pending допускает неопределённые значения (NULL). Сообщение об ошибке говорит, что столбец не допускает значения NULL. Похоже, что эта таблица каким-то образом была создана с иной допустимостью неопределённых значений.

Вот как мы можем воспроизвести эту проблему: используйте графический интерфейс SSMS, чтобы установить этот параметр. В текущем окне запроса щёлкните правой кнопкой мыши и выберите «Параметры запроса…» (Query Options…):

Затем выберите «ANSI» на левой панели и снимите флажок «SET ANSI_NULL_DFLT_ON»:

Теперь попробуйте создать тестовую базу данных и включить 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

Это даёт нам ту же ошибку в 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.

Этот тест показал, что при создании таблицы cdc.change_tables SQL Server 2008 использует настройки клиентской стороны. Когда на клиентской стороне отключён параметр ANSI_NULL_DFLT_ON, столбец has_drop_pending создаётся не допускающим NULL, что и вызывает сбой при включении. Обходной путь — включить ANSI_NULL_DFLT_ON перед включением CDC.

Чтобы дополнительно проверить, сохраняется ли эта проблема для других версий SQL Server, мы повторили тот же тест — включение CDC с отключённым ANSI_NULL_DFLT_ON на стороне клиента. Вот результаты:

• SQL Server 2012 SP3: успешно
• SQL Server 2014 SP2: успешно
• SQL Server 2016 SP1: успешно

Похоже, что эта проблема была исправлена в более новых версиях SQL Server.

И последнее: чтобы проверить текущие настройки соединения, мы всегда можем использовать DBCC USEROPTIONS: