Выявление потерянных записей в Change Tracking (часть 1)

Автор: Mohamed_Baioumy_MSFT, Identifying Orphaned Records in Change Tracking Side Tables (Read‑Only Health Check)

Когда в SQL Server включено отслеживание изменений (CT, Change Tracking), компонент ядра СУБД сохраняет облегчённые метаданные об изменениях, чтобы приложения могли запрашивать: "что изменилось, начиная с версии X?". Внутреннее устройство CT подразумевает наличие:

• Вспомогательной таблицы для каждой отслеживаемой пользовательской таблицы. В ней хранятся метаданные изменений на уровне строк (включая идентификатор транзакции).
• Таблицы фиксации (commit table), в которой хранятся зафиксированные транзакции, затронувшие любую таблицу с включенным отслеживанием изменений. Динамическое административное представление (DMV) sys.dm_tran_commit_table отображает объединение частей этой таблицы фиксации, находящихся в памяти и на диске.

При нормальной работе очистка CT удаляет устаревшие строки из вспомогательных таблиц, а затем — соответствующие более старые строки из таблицы фиксации, основываясь на настроенном периоде хранения. Внутренняя логика очистки использует "безопасную точку очистки", получаемую из настроенного периода хранения, и сопоставляет это астрономическое время с версией CT с помощью системной хранимой процедуры sys.sp_changetracking_time_to_csn.

Однако в некоторых сценариях (обычно связанных с использованием пользовательских или ручных процедур очистки, которые не полностью проверяют результаты удаления из вспомогательных таблиц) записи из таблицы фиксации могут быть удалены, в то время как некоторые строки во вспомогательных таблицах остаются. Это порождает феномен "потерянных" (orphaned) записей: строки во вспомогательных таблицах ссылаются на идентификаторы транзакций, которые больше не существуют в таблице фиксации. Такая ситуация может привести к неуклонному росту объёма данных и появлению симптомов "застревания" очистки CT, поскольку необходимые для корректной очистки данные сопоставления отсутствуют.

В этой статье предлагается сценарий T-SQL, предназначенный только для чтения, который обнаруживает и подсчитывает потерянные записи во всех вспомогательных таблицах CT в базе данных.

Что такое "потерянные" записи CT?

Каждое изменение на уровне строки, захваченное CT, связано с идентификатором транзакции (часто обозначаемым как xdes_id). Вспомогательные таблицы хранят этот идентификатор в столбце sys_change_xdes_id. Таблица фиксации отслеживает зафиксированные транзакции и содержит такие столбцы, как commit_ts и xdes_id.

Потерянная запись CT в данном контексте — это:

Строка во вспомогательной таблице CT, для которой значение sys_change_xdes_id больше не имеет соответствующей строки в таблице фиксации (sys.syscommittab) на момент очистки или ранее.

Почему это важно? Очистка вспомогательных таблиц CT обычно удаляет строки, которые соответствуют записям в таблице фиксации вплоть до точки очистки. Если записи из таблицы фиксации были удалены преждевременно, строки во вспомогательных таблицах становятся "несоответствуемыми", а это означает, что они никогда не будут удалены обычным процессом очистки CT.

Предварительные требования и замечания

• Права доступа / разрешения: Сценарий перечисляет внутренние вспомогательные таблицы CT (из sys.internal_tables, где internal_type = 209) и обращается к sys.syscommittab. Внутренний тип 209 используется именно для вспомогательных таблиц CT.
• Режим "только чтение": Этот сценарий не удаляет данные. Он только подсчитывает и сообщает о потенциальных потерянных записях.
• Производительность: В базах данных с большим количеством таблиц, отслеживаемых CT, или с обширными метаданными CT, выполнение сценария может занять некоторое время. Он выполняет цикл по каждой вспомогательной таблице и проводит проверку существования на основе поиска.
• Стиль вывода: Для вывода результатов по мере выполнения используется RAISERROR ... WITH NOWAIT (полезно при длительных выполнениях).

Сценарий (в исходном виде)

Совет: Замените <[DBName]> на имя вашей базы данных или просто выполните сценарий в правильном контексте базы данных.

-- используйте <[DBName]> -- переключитесь на нужную базу данных

SET NOCOUNT ON

-- находим недопустимую версию очистки на основе настроенного периода хранения
DECLARE @time DATETIME, @csn BIGINT = 0, @minCleanupPoint BIGINT = 0
DECLARE @retention_period INT, @retention_period_units NVARCHAR(10)
SELECT	@retention_period = retention_period,
		@retention_period_units = retention_period_units
		FROM sys.change_tracking_databases where database_id = DB_ID()
SELECT	@time = CASE WHEN @retention_period_units = 1 then DATEADD(minute, (-1 * @retention_period), GETUTCDATE())
		WHEN @retention_period_units = 2 then DATEADD(hour, (-1 * @retention_period), GETUTCDATE())
		ELSE DATEADD(day, (-1 * @retention_period), GETUTCDATE()) END
		
EXEC sp_changetracking_time_to_csn @time = @time, @csn = @csn OUTPUT

SELECT @minCleanupPoint = @csn
SELECT @minCleanupPoint as minCsn -- 688118

-- проходим по всем вспомогательным таблицам отслеживания изменений
DECLARE @sideTable SYSNAME;
DECLARE ct_cursor CURSOR FAST_FORWARD FOR
SELECT name FROM sys.internal_tables WHERE internal_type = 209; -- internal_type = 209 для вспомогательных таблиц отслеживания изменений

OPEN ct_cursor;
FETCH NEXT FROM ct_cursor INTO @sideTable;

WHILE @@FETCH_STATUS = 0
BEGIN
	-- находим минимальный идентификатор xdes_id, срок действия которого истек
	declare @minXdesId BIGINT
	SELECT @minXdesId = min(xdes_id) FROM sys.dm_tran_commit_table where commit_ts <= @minCleanupPoint
	-- SELECT @minXdesId as minXdes
	
	-- создаем временную таблицу для хранения потерянных xdes_id
	DROP TABLE IF EXISTS #OrphanedXdes;
    CREATE TABLE #OrphanedXdes
    (
        sys_change_xdes_id BIGINT NOT NULL
    );

	DECLARE @sql NVARCHAR(MAX);
	SET @sql = N'
    INSERT INTO #OrphanedXdes(sys_change_xdes_id)
    SELECT ct.sys_change_xdes_id
    FROM sys.' + QUOTENAME(@sideTable) + N' AS ct
    WHERE ct.sys_change_xdes_id < @minXdesId
      AND NOT EXISTS
      (
          SELECT 1
          FROM sys.syscommittab AS s
          WHERE s.xdes_id = ct.sys_change_xdes_id AND s.commit_ts <= @minCleanupPoint
      );';

    EXEC sys.sp_executesql
        @sql,
        N'@minXdesId BIGINT, @minCleanupPoint BIGINT',
        @minXdesId = @minXdesId,
		@minCleanupPoint = @minCleanupPoint;

	DECLARE @orphanedIdsCount BIGINT;
	SET @sql = N'
		SELECT @cnt = COUNT_BIG(sys_change_xdes_id)
		FROM #OrphanedXdes;
		';

	EXEC sys.sp_executesql
		@sql,
		N'@cnt BIGINT OUTPUT',
		@cnt = @orphanedIdsCount OUTPUT;

	-- Выдаем ошибку, если существуют какие-либо потерянные xdes_id
	IF (@orphanedIdsCount > 0)
	BEGIN
		DECLARE @msg NVARCHAR(4000) =
			@sideTable + N' : ' + CONVERT(NVARCHAR(30), @orphanedIdsCount);
		RAISERROR (@msg, 16, 1) WITH NOWAIT;
		
		DECLARE @newLine NVARCHAR(10) = CHAR(13) + CHAR(10)
		PRINT (@newLine)
	END

	DROP TABLE IF EXISTS #OrphanedXdes;

    FETCH NEXT FROM ct_cursor INTO @sideTable;
END

CLOSE ct_cursor;
DEALLOCATE ct_cursor;

SET NOCOUNT OFF

Пошаговое описание: что делает сценарий (и зачем)

1) Чтение настроек периода хранения CT для базы данных

Сценарий считывает период хранения CT из представления sys.change_tracking_databases:

• retention_period (числовое значение)
• retention_period_units, где:
  1 = Минуты
  2 = Часы
  3 = Дни

Эти значения определяют, как долго должны храниться метаданные CT, прежде чем очистке будет разрешено их удалить.

2) Вычисление "предельного времени" на основе периода хранения

Сценарий вычитает окно хранения из GETUTCDATE() для вычисления @time, т.е. самой старой точки во времени, которая всё ещё должна сохраняться.

3) Преобразование предельного времени в версию очистки CT (@minCleanupPoint)

Очистка в CT управляется версионной меткой (часто представленной как CSN). Сценарий вызывает процедуру:

sp_changetracking_time_to_csn @time = ..., @csn = ... OUTPUT

...и присваивает полученное значение CSN переменной @minCleanupPoint.

Концептуально это означает: "Укажи версию CT, которая соответствует самому старому времени, подлежащему хранению. Всё, что старше этого, подлежит очистке".

Этот подход соответствует тому, как процессы очистки определяют "безопасную точку очистки" на основе периода хранения.

4) Перечисление вспомогательных таблиц CT (внутренний тип 209)

Вспомогательные таблицы CT хранятся как внутренние таблицы, и сценарий использует запрос:

SELECT name FROM sys.internal_tables WHERE internal_type = 209;

Внутренний тип 209 соответствует вспомогательным таблицам CT.

Имя каждой такой таблицы имеет формат change_tracking_<id> и связано с определенной отслеживаемой пользовательской таблицей.

5) Определение минимального идентификатора транзакции с истекшим сроком действия (@minXdesId)

Таблица фиксации содержит по одной строке на каждую зафиксированную транзакцию, которая затронула отслеживаемые таблицы, и включает столбцы xdes_id и commit_ts. Представление sys.dm_tran_commit_table предоставляет доступ к содержимому таблицы фиксации.

Сценарий вычисляет:

SELECT @minXdesId = min(xdes_id)
FROM sys.dm_tran_commit_table
WHERE commit_ts <= @minCleanupPoint;

Можно рассматривать @minXdesId как:

"Среди записей таблицы фиксации, которые находятся в точке очистки или ранее, каков наименьший идентификатор транзакции, всё ещё присутствующий?"

Это значение используется как оптимизационная граница на следующем шаге.

6) Для каждой вспомогательной таблицы — захват потерянных значений sys_change_xdes_id

Внутри динамического SQL (поскольку имя вспомогательной таблицы меняется на каждой итерации) происходит вставка во временную таблицу всех значений sys_change_xdes_id, которые удовлетворяют двум критериям:

• Старше минимального идентификатора транзакции с истекшим сроком действия

  ct.sys_change_xdes_id < @minXdesId

  Это сужает область сканирования до кандидатов, которые определенно старше текущей "самой старой известной" транзакции в таблице фиксации.

• Нет соответствующей строки в таблице фиксации вплоть до точки очистки

  NOT EXISTS (
    SELECT 1
    FROM sys.syscommittab AS s
    WHERE s.xdes_id = ct.sys_change_xdes_id
      AND s.commit_ts <= @minCleanupPoint
  )

  sys.syscommittab хранит постоянную (на диске) таблицу фиксации. Таблица фиксации включает столбцы commit_ts и xdes_id и имеет уникальный некластерный индекс по (xdes_id), что делает эту проверку существования эффективной при наличии доступа.

Если строка во вспомогательной таблице ссылается на идентификатор транзакции, который больше не существует в таблице фиксации (в пределах соответствующего горизонта очистки), она считается потерянной.

Этот шаблон соответствует задокументированному подходу к очистке, где вспомогательные таблицы очищаются на основе условия sys_change_xdes_id IN (SELECT xdes_id FROM sys.syscommittab WHERE commit_ts <= @csn). Таким образом, если xdes_id отсутствует в sys.syscommittab, строка во вспомогательной таблице не может быть сопоставлена для очистки.

7) Подсчет и отчет по каждой вспомогательной таблице

Для каждой вспомогательной таблицы подсчитывается количество строк в #OrphanedXdes. Если оно больше 0, выводится сообщение:

change_tracking_XXXXXXXX : <количество>

с использованием RAISERROR ... WITH NOWAIT, что позволяет видеть прогресс в реальном времени.

Интерпретация результатов

• Отсутствие вывода (или отсутствие строк, выданных через RAISERROR) обычно означает, что потерянные строки по критериям сценария не обнаружены.
• Вывод, подобный этому:
  change_tracking_2140287130 : 12345
  означает, что данная вспомогательная таблица содержит 12 345 потерянных ссылок на транзакции (т.е. значения sys_change_xdes_id, не найденные в таблице фиксации вплоть до точки очистки).

Если вы видите потерянные записи во многих вспомогательных таблицах, это часто указывает на системную проблему (например, задание ручной очистки, которое удаляет данные из таблицы фиксации, даже когда удаление из вспомогательных таблиц завершается ошибкой). Именно такой сценарий отказа — удаление из таблицы фиксации продолжается, даже если при удалении из вспомогательной таблицы произошли ошибки — был описан в реальных исследованиях как первопричина появления потерянных строк во вспомогательных таблицах CT.

Почему возникают потерянные записи (типичный сценарий)

Повторяющийся сценарий таков:

1. Логика очистки определяет строки вспомогательной таблицы, подлежащие удалению, на основе commit_ts из таблицы фиксации / версии очистки.
2. Удаление из вспомогательной таблицы сталкивается с блокировками/конкуренцией и не удаляет все строки.
3. Очистка, тем не менее, переходит к удалению строк таблицы фиксации.
4. Оставшиеся строки вспомогательной таблицы теперь ссылаются на значения xdes_id, которых больше нет в таблице фиксации → потерянные записи.

Эта последовательность явно упоминается во внутренних обсуждениях архитектуры и полевых наблюдениях: сначала должны очищаться вспомогательные таблицы, и удаление из таблицы фиксации должно происходить только после успешной очистки вспомогательных таблиц.

Операционные советы и безопасные дальнейшие шаги

1) Запускайте это для периодической проверки

Если вы работаете в крупном масштабе (много баз данных / много отслеживаемых таблиц), периодический запуск этого сценария поможет вам обнаруживать отклонения на ранней стадии — до того, как рост метаданных CT станет болезненным.

2) Если вы нашли потерянные записи, рассматривайте это как симптом, а не первопричину

Перед исправлением проверьте:

• Включена ли автоматическая очистка (is_auto_cleanup_on)?
• Не выполняются ли какие-либо пользовательские или ручные процедуры очистки, которые затрагивают внутренние структуры CT?

Эти значения видны через представление sys.change_tracking_databases.

3) Согласуйте поведение ручной очистки с задокументированной последовательностью действий

Общий принцип очистки заключается в том, что удаление из вспомогательных таблиц должно быть скоординировано с очисткой таблицы фиксации (и предшествовать ей). В описании архитектуры очистки упоминается получение точки очистки с помощью sys.sp_changetracking_time_to_csn и последующее удаление строк из вспомогательных таблиц пакетами на основе членства в таблице фиксации.

Возможные улучшения (для удобства работы)

Если вы хотите сделать этот сценарий еще более удобным для оператора (оставаясь в режиме только чтения), рассмотрите возможность:

• Возврата результирующего набора вместо (или в дополнение к) RAISERROR:
  ИмяВспомогательнойТаблицы, КоличествоПотерянных, МинПотерянныйXdes, МаксПотерянныйXdes
• Агрегации итоговых сумм по всем вспомогательным таблицам в конце.
• Добавления времени выполнения по каждой таблице для выявления "горячих" точек.

Это чисто улучшения удобства использования; основная логика обнаружения остается неизменной.