WIPI

프로그래밍과 기술 이슈를 빠르게 정리하는 개발 블로그

목록으로

Programming Notes

Part 2: 변경 추적 사이드 테이블에서 고아 레코드를 안전하게 정리하기

적용 대상: Azure SQL Database (변경 추적 활성화됨) 요약 (Part 1) Part 1 에서는 변경 추적(CT) 사이드 테이블에서 "고아(orphaned)" 레코드를 감지하는 방법을 다뤘습니다. 이는 sys_change_xdes_id 가 커밋 테이블(...

작성자

적용 대상: Azure SQL Database (변경 추적 활성화됨)

요약 (Part 1)

Part 1에서는 변경 추적(CT) 사이드 테이블에서 "고아(orphaned)" 레코드를 감지하는 방법을 다뤘습니다. 이는 sys_change_xdes_id가 커밋 테이블(sys.syscommittab)에서 일치하는 트랜잭션 항목이 더 이상 없는 행을 의미합니다. 이러한 상황은 종종 예상치 못한 CT 증가와 "정리 중단" 증상으로 이어지는데, 이는 일반적인 정리에 필요한 매핑 데이터가 누락되었기 때문입니다.

Part 1 링크: 변경 추적 사이드 테이블에서 고아 레코드 식별 (읽기 전용 상태 점검)

Part 2가 필요한 이유

현장에서 흔히 볼 수 있는 "근본 패턴"은 다음과 같습니다:

  1. 사이드 테이블 정리가 만료된 메타데이터를 삭제 시도
  2. 일부 사이드 테이블 삭제 실패 (잠금/시간 초과/오류)
  3. 커밋 테이블 정리가 그대로 진행됨 (또는 사용자 지정 워크플로가 사이드 테이블 삭제를 검증하지 않고 커밋 테이블에서 삭제함)
  4. 남아 있는 사이드 테이블 행이 더 이상 sys.syscommittab에 존재하지 않는 xdes_id 값을 참조함 → 고아 발생

Microsoft Learn에서도 syscommittab 정리가 사이드 테이블 정리에 달려 있음을 강조합니다. 즉, 사이드 테이블이 정리된 후에만 커밋 테이블 정리가 이루어져야 합니다.

이 Part 2 스크립트는 사이드 테이블에서 고아 행을 제거하는 데 중점을 둡니다 (sys.syscommittab는 건드리지 않음). 따라서 정리 로직이 다시 안정화될 수 있습니다.

중요한 전제 조건 및 제약 사항 (먼저 읽어주세요)

1) Azure SQL Database의 내부 테이블 액세스

Azure SQL Database에서 고객은 특정 내부 CT 아티팩트에 직접 액세스하지 못할 수 있습니다 (DAC 스타일 워크플로를 시도하더라도). 관련 사례 토론에서 내부 테스트 결과 내부 테이블에 대한 셀프 서비스 정리가 실행 불가능할 수 있음이 확인되었습니다.

2) CHECKPOINT 참고 (스크립트에 포함된 이유)

sys.dm_tran_commit_table은 커밋 테이블 데이터를 노출하며 sys.syscommittab에 의해 지원됩니다. Microsoft Learn은 읽기 전용 사용자는 CHECKPOINT가 발생하기 전까지 라이브 변경 사항을 보지 못할 수 있음을 언급합니다. 따라서 스크립트에 커밋 테이블 상태를 읽기 전에 선택적 CHECKPOINT 주석이 포함되어 있습니다.

3) 지원되는 지침 vs. 사용자 지정 완화

Microsoft Learn은 CT 정리 문제에 대한 공식적인 문제 해결/완화 지침을 제공합니다 (dbo.MSChange_tracking_history 확인, 오래된 행 평가, sp_flush_commit_table_on_demand를 사용한 커밋 테이블 정리 포함). 이 스크립트는 특정 실패 모드(고아 사이드 테이블 행)에 대한 맞춤형 수정 패턴입니다. 신중하게 사용하고, 먼저 테스트하며, 조직의 승인 절차를 따르세요.

이 스크립트가 하는 일 (개요)

T-SQL 스크립트는 기본적으로 Part 1 감지 + 선택적 대상 삭제 생성입니다:

  1. sp_changetracking_time_to_csn을 사용하여 CT 보존 기간(실제 시간 → CSN)에서 "안전한 정리 지점"을 계산합니다 — Part 1에서 사용된 것과 동일한 개념입니다.
  2. internal_type = 209 (CT 사이드 테이블)인 sys.internal_tables를 통해 CT 사이드 테이블을 열거합니다.
  3. 각 사이드 테이블에 대해, 다음 조건을 만족하는 고아 트랜잭션 ID 후보(sys_change_xdes_id)를 식별합니다:
    • 계산된 경계(@minXdesId, sys.dm_tran_commit_table에서 파생)보다 오래되었고,
    • 정리 지점 또는 그 이전에 sys.syscommittab에 일치하는 xdes_id가 없음.
  4. RAISERROR ... WITH NOWAIT를 사용하여 사이드 테이블별 고아 개수를 출력합니다 (운영자 친화적인 스트리밍 출력).
  5. 안전 교차 검증: 예상치 않게 sys.syscommittab에 "고아"가 존재하는 경우 중단합니다 (방어적인 정상 작동 게이트).
  6. 현재 사이드 테이블에 대한 DELETE 문을 생성합니다 (실행은 주석 처리되어 있습니다).

스크립트 (Part 2) — "감지 + 삭제 생성"

다음은 T-SQL 스크립트입니다. 삭제 단계는 기본적으로 비활성화되어 있어 안전하게 공유할 수 있습니다. (승인/테스트 후에만 실행을 활성화할 수 있습니다.)

-- use <[DBName]> -- 올바른 데이터베이스로 전환하세요

-- 먼저 체크포인트를 실행하여 모든 인메모리 커밋 테이블 데이터가 디스크에 유지되도록 합니다 (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

    -- syscommittab에 xdes가 존재하지 않아야 함을 교차 확인
    -- !!!중요!!! 존재하는 경우 오류를 발생시키고 정리를 중단합니다
    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''정리 중단: 사이드 테이블 ['' + @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> 이는 해당 CT 사이드 테이블에 <count>개의 고아 트랜잭션 참조가 있음을 나타냅니다. 이는 Part 1에서 장기간 스트리밍 진행 상황을 표시하는 데 사용된 것과 동일한 스타일입니다.

다음 단계 (권장 순서)

  1. 공식 지침을 사용하여 CT 구성 (보존 기간 + AUTO_CLEANUP 상태)을 확인합니다.
  2. Part 1 / Part 2 감지를 실행하여 범위(어떤 사이드 테이블, 몇 개)를 정량화합니다.
  3. 수정해야 하는 경우:
    • 가능하다면 지원되는 완화 방법을 선호합니다 (예를 들어, 특정 정리 잠금 충돌 시나리오에서 테이블의 추적 메타데이터를 제거하기 위해 테이블에 대한 CT 비활성화/활성화는 Microsoft Learn에 "가장 빠른 해결책"으로 나열되어 있습니다).
    • 테이블 수준 비활성화/활성화가 허용되지 않는 경우, 승인 기반 접근 방식을 사용하여 대상 정리를 수행합니다.

마무리

고아 CT 사이드 테이블 레코드는 저장 공간이나 CHANGETABLE 성능이 문제가 될 때까지 쉽게 놓칠 수 있는 "조용한 증가" 조건 중 하나입니다. Part 1은 문제를 일찍 발견하는 데 도움이 되며, Part 2는 명시적인 안전 장치와 기본적으로 비활성화된 삭제 단계가 포함된 안전하고 표적화된 정리 워크플로를 준비하는 데 도움이 됩니다.

참조