Автор:
Mohamed_Baioumy_MSFT,
Part 2: Safely Cleaning Orphaned Records in Change Tracking Side Tables
Область применения: База данных SQL Azure (с включенным отслеживанием изменений)
Краткое повторение (Часть 1)
В Части 1 мы рассмотрели, как обнаружить ""Orphaned записи во вспомогательных таблицах отслеживания изменений — строки, чей sys_change_xdes_id больше не имеет соответствующей записи транзакции в таблице фиксации (sys.syscommittab). Эта ситуация часто приводит к неожиданному росту размера отслеживания изменений и симптомам "stuck cleanup", потому что данные сопоставления, необходимые для нормальной очистки, отсутствуют.
Ссылка на Часть 1: Выявление потерянных записей в Change Tracking (часть 1)
Почему нужна Часть 2
Распространенный "корневой шаблон", который мы наблюдаем на практике:
- Очистка вспомогательных таблиц пытается удалить устаревшие метаданные
- Некоторые удаления из вспомогательных таблиц завершаются сбоем (блокировки, тайм-ауты, ошибки)
- Очистка таблицы фиксации всё равно выполняется (или пользовательский рабочий процесс удаляет данные из таблицы фиксации без проверки удаления из вспомогательных таблиц)
- Оставшиеся строки вспомогательных таблиц теперь ссылаются на значения xdes_id, которые больше не существуют в sys.syscommittab → образуются потерянные записи
В документации Microsoft Learn также подчеркивается, что очистка syscommittab зависит от очистки вспомогательных таблиц — очистка таблицы фиксации должна происходить только после очистки вспомогательных таблиц.
Этот скрипт из Части 2 сосредоточен на удалении потерянных, осиротевших строк из вспомогательных таблиц (и не затрагивает sys.syscommittab), чтобы логика очистки могла снова стабилизироваться.
Важные предварительные требования и ограничения (прочтите это сначала)
1) Доступ к внутренним таблицам в Базе данных SQL Azure
В Базе данных SQL Azure клиенты могут не иметь возможности напрямую обращаться к определенным внутренним артефактам отслеживания изменений (даже при попытке использования рабочих процессов в стиле DAC). В соответствующих обсуждениях случаев внутреннее тестирование показало, что самостоятельная очистка внутренних таблиц может быть неосуществима.
2) Примечание о CHECKPOINT (почему он в скрипте)
sys.dm_tran_commit_table предоставляет данные таблицы фиксации и поддерживается sys.syscommittab. В документации Microsoft Learn отмечается, что пользователи, имеющие доступ только для чтения, могут не видеть текущие изменения до выполнения CHECKPOINT.
Вот почему в ваш скрипт включен необязательный комментарий CHECKPOINT перед чтением состояния таблицы фиксации.
3) Поддерживаемые рекомендации против пользовательского исправления
В Microsoft Learn предоставлены официальные рекомендации по устранению неполадок и смягчению проблем с очисткой отслеживания изменений (включая проверку dbo.MSChange_tracking_history, оценку устаревших строк и использование sp_flush_commit_table_on_demand для очистки таблицы фиксации).
Этот скрипт представляет собой целенаправленный шаблон исправления для конкретного режима сбоя (осиротевшие строки вспомогательных таблиц). Используйте его осторожно, сначала протестируйте и следуйте процедурам, принятым в вашей организации.
Что делает этот скрипт (общий обзор)
Скрипт T-SQL, по сути, представляет собой обнаружение из Части 1 + дополнительная необязательная генерация необходимого для удаления кода:
- Вычисляет "безопасную точку очистки" из периода хранения отслеживания изменений (календарное время → CSN) с помощью sp_changetracking_time_to_csn — та же концепция, что использовалась в Части 1.
- Перечисляет вспомогательные таблицы отслеживания изменений через sys.internal_tables, где internal_type = 209 (вспомогательные таблицы отслеживания изменений).
- Для каждой вспомогательной таблицы определяет кандидатные осиротевшие идентификаторы транзакций (sys_change_xdes_id), которые:
- старше вычисленной границы (@minXdesId, полученной из sys.dm_tran_commit_table), и
- не имеют соответствующего xdes_id в sys.syscommittab на/до точки очистки
- Выводит количество осиротевших записей для каждой вспомогательной таблицы с помощью RAISERROR … WITH NOWAIT (удобный для оператора потоковый вывод).
- Перекрестная проверка безопасности: прерывает работу, если какая-либо "осиротевшая" запись неожиданно существует в sys.syscommittab (защита от "дурака").
- Генерирует инструкцию DELETE для текущей вспомогательной таблицы (выполнение закомментировано).
Скрипт (Часть 2) — "обнаружение + генерация удаления"
Ниже приведен скрипт T-SQL. Я оставил шаг удаления отключенным по умолчанию, чтобы его можно было безопасно распространять. (Вы можете включить выполнение только после согласования/тестирования.)
-- use <[DBName]> -- переключиться на нужную базу данных
-- сначала выполните checkpoint, чтобы убедиться, что все данные таблицы фиксации в памяти сохранены на диск (syscommittab)
-- checkpoint
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
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
-- Перекрестная проверка: ни один xdes не должен существовать в syscommittab
-- !!!ВАЖНО!!! Вызвать ошибку и остановить очистку, если это произошло
SET @sql = N'
DECLARE @nonMatchingXdesCount BIGINT;
SELECT @nonMatchingXdesCount = COUNT_BIG(*)
FROM #OrphanedXdes AS ct
WHERE EXISTS (
SELECT 1
FROM sys.syscommittab AS s
WHERE s.xdes_id = ct.sys_change_xdes_id
);
-- SELECT @nonMatchingXdesCount as nonMatchingXdesCount
IF (COALESCE(@nonMatchingXdesCount, 0) > 0)
BEGIN TRY
DECLARE @msg NVARCHAR(1024);
SET @msg = N''Cleanup aborted: orphan cross-check failed for side table [' + @sideTable + N'].'';
RAISERROR(@msg, 16, 1) WITH NOWAIT;
RETURN;
END TRY
BEGIN CATCH
THROW;
END CATCH
';
EXEC sys.sp_executesql @sql;
IF (@orphanedIdsCount > 0)
BEGIN
-- Подготовить запрос для удаления осиротевших строк из вспомогательной таблицы
SET @sql = N'DELETE ct FROM sys.' + @sideTable + N' ct WHERE EXISTS (SELECT 1 FROM #OrphanedXdes AS o WHERE o.sys_change_xdes_id = ct.sys_change_xdes_id);';
SELECT @sql -- проверить, что запрос на удаление сгенерирован правильно
-- Пример инструкции удаления: DELETE ct FROM sys.change_tracking_1221579390 ct WHERE EXISTS (SELECT 1 FROM #OrphanedXdes AS o WHERE o.sys_change_xdes_id = ct.sys_change_xdes_id);
-- ПРИМЕЧАНИЕ: Раскомментируйте приведенный ниже запрос, чтобы выполнить инструкцию удаления и удалить осиротевшие записи
-- EXEC sys.sp_executesql @sql;
END
DROP TABLE IF EXISTS #OrphanedXdes;
FETCH NEXT FROM ct_cursor INTO @sideTable;
END
CLOSE ct_cursor;
DEALLOCATE ct_cursor;
SET NOCOUNT OFF
Почему "перекрестная проверка с прерыванием" — это хорошая идея
Обратите внимание на защитную проверку:
- Вы сначала определяете осиротевшие записи как те, которые не существуют в sys.syscommittab (для горизонта очистки).
- Затем вы перепроверяете: "Если какая-либо из них появляется в syscommittab, прервать выполнение".
Это предотвращает случайное удаление, если:
- расчет горизонта очистки неверен,
- в среде есть неожиданные различия в видимости, или
- содержимое временной таблицы не соответствует ожидаемому.
Эта перестраховочная схема хорошо согласуется с общим принципом, задокументированным в Microsoft Learn: очистка таблицы фиксации должна происходить только после очистки вспомогательных таблиц, а устранение неполадок должно быть основано на данных и выполняться с осторожностью.
Как интерпретировать вывод
- Если вы не видите строк RAISERROR, скрипт не обнаружил осиротевших строк в соответствии с заданными критериями.
- Если вы видите:
change_tracking_<id> : <count> это указывает на <count> осиротевших ссылок на транзакции в этой вспомогательной таблице отслеживания изменений. Это тот же стиль, который использовался в Части 1 для длительного потокового вывода прогресса.
Следующие шаги (рекомендуемый порядок)
- Подтвердите конфигурацию отслеживания изменений (период хранения + статус AUTO_CLEANUP), используя официальные рекомендации.
- Запустите обнаружение из Части 1 / Части 2, чтобы количественно оценить масштаб (какие вспомогательные таблицы, сколько).
- Если вам нужно провести исправление:
- Предпочитайте поддерживаемые меры смягчения, где это возможно (например, отключение/включение отслеживания изменений для таблицы, чтобы очистить метаданные отслеживания для этой таблицы, указано как "самое быстрое средство" в Microsoft Learn для определенных сценариев конфликтов блокировок при очистке).
- Если отключение/включение на уровне таблицы неприемлемо, используйте подход, основанный на утверждении, для целевой очистки.
Заключение
Осиротевшие записи во вспомогательных таблицах отслеживания изменений — это одно из тех состояний "тихого роста", которое легко пропустить, пока не станет болезненным либо использование хранилища, либо производительность CHANGETABLE. Часть 1 помогает вам обнаружить проблему на ранней стадии; Часть 2 помогает подготовить безопасный, целевой рабочий процесс очистки — с явными защитными проверками и шагом удаления, который остается отключенным по умолчанию.
Ссылки
