변경 내용 추적 자동 정리 문제 해결

아티클
07/12/2024

적용 대상: SQL Server Azure SQL 데이터베이스 Azure SQL Managed Instance

이 문서에서는 변경 내용 추적 자동 정리에서 볼 수 있는 일반적인 문제의 해결 방법을 알려드립니다.

증상

일반적으로, 자동 정리가 예상대로 작동하지 않는 경우 다음 증상 중 하나 이상이 나타날 수 있습니다.

하나 이상의 변경 내용 추적 사이드 테이블 또는 syscommittab 시스템 테이블에 의한 높은 스토리지 사용량.
사이드 테이블(이름이 접두사 change_tracking(으)로 시작하는 내부 테이블, 예: change_tracking_12345) 또는 syscommittab 둘 다, 구성된 보존 기간을 벗어나는 많은 수의 행을 표시합니다.
dbo.MSChange_tracking_history 테이블에 특정 정리 오류가 있는 항목이 있습니다.
CHANGETABLE 성능이 시간이 지남에 따라 저하되었습니다.
자동 정리 또는 수동 정리에서 높은 CPU 사용량이 보고됩니다.

디버깅 및 완화

변경 내용 추적 자동 정리와 관련된 문제의 근본 원인을 식별하려면 다음 단계에 따라 문제를 디버그하고 완화합니다.

자동 정리 상태

자동 정리가 실행 중인지 확인합니다. 이를 확인하려면 동일한 데이터베이스에서 정리 기록 테이블을 쿼리합니다. 정리가 실행되고 있는 경우 테이블에 정리 시작 및 종료 시간이 포함된 항목이 있습니다. 정리가 실행되고 있지 않은 경우 테이블이 비어 있거나 테이블에 부실 항목이 있습니다. 기록 테이블의 열 comments에 태그 cleanup errors이(가) 있는 항목이 있는 경우 테이블 수준 정리 오류로 인해 정리가 실패합니다.

SELECT TOP 1000 * FROM dbo.MSChange_tracking_history ORDER BY start_time DESC;

자동 정리는 기본 간격을 30분으로 하여 주기적으로 실행됩니다. 기록 테이블이 없는 경우 자동 정리가 실행되지 않을 가능성이 높습니다. 그렇지 않으면 start_time 및 end_time 열 값을 확인합니다. 최신 항목이 최신 상태가 아니고 몇 시간 또는 며칠이 지난 경우 자동 정리가 실행되지 않을 수 있습니다. 이 경우 다음 단계에 따라 문제를 해결합니다.

1. 정리가 꺼져 있음

데이터베이스에 대한 자동 정리가 설정되었는지 확인합니다. 그렇지 않은 경우에는 해당 설정을 실행하고 30분 이상 기다린 후에 기록 테이블에서 새 항목을 찾아봅니다. 이후 기록 테이블의 진행률을 모니터링합니다.

SELECT * FROM sys.change_tracking_databases WHERE database_id=DB_ID('<database_name>')

is_auto_cleanup_on에서 0이 아닌 값은 자동 정리를 사용함을 나타냅니다. 보존 기간 값에 따라 변경 내용 추적 메타데이터를 시스템에 보존하는 기간이 달라집니다. 변경 내용 추적 보존 기간의 기본값은 2일입니다.

변경 내용 추적을 사용하거나 사용하지 않으려면 변경 내용 추적 사용 및 사용 안 함(SQL Server)을 참조하세요.

2. 정리가 켜져 있지만 실행되지 않음

자동 정리가 켜진 경우 예기치 않은 오류로 인해 자동 정리 스레드가 중지되었을 수 있습니다. 현재, 자동 정리 스레드를 다시 시작하는 것은 불가능합니다. 보조 서버에 대한 장애 조치(failover)를 시작(하거나 보조 서버가 없는 경우 서버를 다시 시작)하고 데이터베이스에 대해 자동 정리 설정을 사용 중인지 확인해야 합니다.

자동 정리가 실행되지만 진행되지 않음

하나 이상의 사이드 테이블에 상당한 스토리지 사용량이 표시되어 있거나 구성된 보존 기간을 초과하는 많은 수의 레코드가 포함되어 있는 경우, 단일 사이드 테이블에 대한 해결 방법이 설명되어 있는 이 섹션의 단계에 따릅니다. 필요한 경우 더 많은 테이블에 대해 동일한 단계를 반복할 수 있습니다.

1. 자동 정리 백로그 평가

만료된 레코드의 큰 백로그가 있는 사이드 테이블을 식별합니다. 이 테이블에 대해 완화를 수행해야 합니다. 다음 쿼리를 실행하여 만료된 레코드 수가 많은 사이드 테이블을 식별합니다. 예시 스크립트의 값을 아래와 같이 바꿔야 합니다.

잘못된 정리 버전을 가져옵니다.
```
SELECT * FROM sys.change_tracking_tables;
```
반환된 행의 cleanup_version 값은 잘못된 정리 버전을 나타냅니다.

다음 동적 T-SQL(Transact-SQL) 쿼리를 실행합니다. 그러면 사이드 테이블의 만료된 행 수를 가져오는 쿼리가 생성됩니다. 쿼리의 <invalid_version> 값을 이전 단계에서 얻은 값으로 바꿉니다.

SELECT 'SELECT ''' + QUOTENAME(name) + ''', count(*) FROM [sys].' + QUOTENAME(name)
    + ' WHERE sys_change_xdes_id IN (SELECT xdes_id FROM sys.syscommittab ssct WHERE ssct.commit_ts <= <invalid_version>) UNION'
FROM sys.internal_tables
WHERE internal_type = 209;

이전 쿼리에서 결과 집합을 복사하고 마지막 행에서 UNION 키워드를 제거합니다. DAC(관리자 전용 연결)를 통해 생성된 T-SQL 쿼리를 실행하는 경우, 쿼리가 모든 사이드 테이블의 만료된 행 수를 알려줍니다. sys.syscommittab 테이블의 크기와 사이드 테이블 수에 따라 이 쿼리를 완료하는 데 시간이 오래 걸릴 수 있습니다.

Important

이 단계는 완화 단계를 진행하기 위해 필요합니다. 이전 쿼리가 실행에 실패한 경우 다음에 지정된 쿼리를 사용하여 개별 사이드 테이블의 만료된 행 수를 식별합니다.

자동 정리가 완료될 수 있도록 만료된 행 수가 관리 가능한 상태에 이를 때까지, 만료된 행 수를 내림차순으로 하여 사이드 테이블에 대해 다음 완화 단계를 수행합니다.

만료된 레코드 수가 많은 사이드 테이블을 식별한 후, 사이드 테이블 삭제 문의 대기 시간 및 지난 몇 시간 동안의 초당 삭제 속도에 대한 정보를 수집합니다. 다음으로 부실 행 수와 삭제 대기 시간을 모두 고려하여 사이드 테이블 정리에 필요한 시간을 예측합니다.

매개 변수 템플릿을 적절한 값으로 대체하여 다음 T-SQL 코드 조각을 사용합니다.

초당 정리 속도를 쿼리합니다.

SELECT
    table_name,
    rows_cleaned_up / ISNULL(NULLIF(DATEDIFF(second, start_time, end_time), 0), 1),
    cleanup_version
FROM dbo.MSChange_tracking_history
WHERE table_name = '<table_name>'
ORDER BY end_time DESC;

DATEDIFF 함수에 분 또는 시간 단위를 사용할 수도 있습니다.

추가 테이블에서 부실 행 수를 찾습니다. 해당 쿼리는 정리 보류 중인 행 수 찾기에 도움을 줍니다.

사용자 테이블의 <internal_table_name> 및 <cleanup_version>은(는) 이전 섹션에서 반환된 출력에 있습니다. 이 정보를 사용하여 DAC(관리자 전용 연결)를 통해 다음 T-SQL 코드를 실행합니다.
```
SELECT '<internal_table_name>',
    COUNT(*)
FROM sys.<internal_table_name>
WHERE sys_change_xdes_id IN (
        SELECT xdes_id
        FROM sys.syscommittab ssct
        WHERE ssct.commit_ts <= <cleanup_version>
);
```
해당 쿼리는 완료하는 데 시간이 걸릴 수 있습니다. 쿼리 시간이 초과되는 경우 요약 행과 활성 행(즉, 정리할 행)의 차이를 알아내 부실 행을 계산합니다.

다음 쿼리를 실행하여 사이드 테이블의 총 행 수를 알아냅니다.

SELECT sum(row_count) FROM sys.dm_db_partition_stats
WHERE object_id = OBJECT_ID('sys.<internal_table_name>')
GROUP BY partition_id;

다음 쿼리를 실행하여 사이드 테이블의 활성 행 수를 알아냅니다.
```
SELECT '<internal_table_name>', COUNT(*) FROM sys.<internal_table_name> WHERE sys_change_xdes_id
IN (SELECT xdes_id FROM sys.syscommittab ssct WHERE ssct.commit_ts > <cleanup_version>);
```
정리 및 부실 행 수를 사용하여 테이블 정리에 걸리는 예상 시간을 계산할 수 있습니다. 다음 수식을 고려합니다.

정리에 걸리는 시간(분) = (부실 행 수) / (정리 속도(분))

테이블 정리를 완료하는 시간이 허용되는 경우 진행률을 모니터링하고 자동 정리 작업을 계속하도록 합니다. 그렇지 않은 경우 다음 단계를 진행하여 추가로 드릴다운합니다.

2. 테이블 잠금 충돌 확인

테이블 잠금 에스컬레이션 충돌로 인하여 정리가 진행되지 않는지 확인합니다. 해당 충돌로 인해 행을 삭제하여 정리하기 위한 사이드 테이블의 잠금을 지속적으로 획득하지 못하고 있습니다.

잠금 충돌을 확인하려면 다음 T-SQL 코드를 실행합니다. 해당 쿼리는 문제가 있는 테이블에 대한 레코드를 불러와 잠금 충돌을 의미하는 여러 항목이 있는지 확인합니다. 일정 기간 동안 산발적으로 나타나는 몇 번의 충돌은 진행 중인 완화 단계에 적합하지 않습니다. 충돌은 되풀이되어야 합니다.

SELECT TOP 1000 *
FROM dbo.MSChange_tracking_history
WHERE table_name = '<user_table_name>'
ORDER BY start_time DESC;

기록 테이블에 값이 Cleanup error: Lock request time out period exceeded인 comments 열에 여러 항목이 있다면 이는 잠금 충돌 또는 잠금 시간 제한으로 인해 여러 번의 정리 시도가 실패했음을 명확하게 나타냅니다. 다음 해결 방법을 고려하세요.

문제가 있는 테이블에서 변경 내용 추적을 사용하지 않음 설정하고 사용 설정합니다. 이렇게 하면 테이블이 제거될 기본 추적 메타데이터가 전부 제거됩니다. 테이블의 데이터는 손상되지 않고 유지됩니다. 이것이 가장 빠른 해결책입니다.
이전 옵션을 사용할 수 없는 경우, 다음과 같이 추적 플래그 8284를 사용하도록 설정하여 테이블에서 수동 정리를 실행합니다.
```
DBCC TRACEON (8284, -1);
GO
EXEC [sys].[sp_flush_CT_internal_table_on_demand] @TableToClean = '<table_name>';
```

3. 다른 원인 확인

정리 지연의 또 다른 원인은 삭제 문의 속도 저하입니다. 그러한지 확인하려면 hardened_cleanup_version의 값을 검사합니다. 이 값은 고려 중인 데이터베이스에 대한 DAC(관리자 전용 연결)를 통해 검색할 수 있습니다.

다음 쿼리를 실행하여 강화된 정리 버전을 알아냅니다.

SELECT * FROM sys.sysobjvalues WHERE valclass = 7 AND objid = 1004;

다음 쿼리를 실행하여 정리 버전을 알아냅니다.

SELECT * FROM sys.sysobjvalues WHERE valclass = 7 AND objid = 1003;

hardened_cleanup_version 및 cleanup_version 값이 같으면 이 섹션을 건너뛰고 다음 섹션으로 진행합니다.

두 값이 서로 다르면 하나 이상의 사이드 테이블에 오류가 발생했음을 의미합니다. 가장 빠른 완화 방법은 문제가 있는 테이블에서 변경 내용 추적을 사용 안 함 및 사용으로 설정하는 것입니다. 이렇게 하면 테이블이 제거될 기본 추적 메타데이터가 전부 제거됩니다. 테이블의 데이터는 손상되지 않고 유지됩니다.

이전 옵션을 사용할 수 없는 경우 테이블에서 수동 정리를 실행합니다.

syscommittab 문제 해결

syscommittab 시스템 테이블에서 많은 저장소 공간을 사용하거나 부실 행의 큰 백로그가 있는 경우, 이 섹션에서 해당 테이블의 문제를 디버그하고 완화하는 단계를 설명합니다.

syscommittab 시스템 테이블 정리는 사이드 테이블 정리에 따라 달라집니다. 모든 사이드 테이블이 정리된 후에만 syscommittab을 제거할 수 있습니다. 자동 정리가 실행되지만 진행되지 않음 섹션의 모든 단계를 수행하는지 확인합니다.

syscommittab 정리를 명시적으로 호출하려면 sys.sp_flush_commit_table_on_demand 저장 프로시저를 사용합니다.

참고 사항

sys.sp_flush_commit_table_on_demand 저장 프로시저는 행의 큰 백로그를 삭제하는 경우 시간이 걸릴 수 있습니다.

sys.sp_flush_commit_table_on_demand 문서의 예시 섹션과 같이 이 저장 프로시저는 safe_cleanup_version()의 값과 삭제된 행의 수를 반환합니다. 반환된 값이 0인 것으로 나타나고 스냅샷 격리가 켜져 있으면 정리 작업을 하더라도 syscommittab에서 아무것도 삭제되지 않을 수 있습니다.

보존 기간이 1일보다 크면 추적 플래그 8239를 전역적으로 사용한 후 sys.sp_flush_commit_table_on_demand 저장 프로시저를 다시 실행해도 안전합니다. 스냅샷 격리가 해제되어 있을 때 이 추적 플래그를 사용하는 것이 항상 안전하지만 어떤 경우에는 불필요할 수도 있습니다.

정리 중 높은 CPU 사용률

이 섹션에 설명된 문제는 이전 버전의 SQL Server에서 확인할 수 있습니다. 데이터베이스에 변경 내용 추적 테이블이 많고 자동 정리 또는 수동 정리로 인해 CPU 사용률이 높은 경우. 이 문제는 기록 테이블로 인해 발생할 수도 있습니다. 이에 대해서는 이전 섹션에서 간략하게 설명했습니다.

다음 T-SQL 코드를 사용하여 기록 테이블의 행 수를 확인합니다.

SELECT COUNT(*) from dbo.MSChange_tracking_history;

행 수가 충분히 큰 경우, 다음 인덱스가 없으면 추가해 보세요. 다음 T-SQL 코드를 사용하여 인덱스를 추가합니다.

IF NOT EXISTS (
    SELECT *
    FROM sys.indexes
    WHERE name = 'IX_MSchange_tracking_history_start_time'
        AND object_id = OBJECT_ID('dbo.MSchange_tracking_history')
)
BEGIN
    CREATE NONCLUSTERED INDEX IX_MSchange_tracking_history_start_time
    ON dbo.MSchange_tracking_history (start_time)
END

30분보다 더 자주 정리 실행

특정 테이블은 변동률이 높을 수 있으며 자동 정리 작업은 30분 간격 내에 사이드 테이블 및 syscommittab을 정리할 수 없습니다. 이 경우 빈도가 증가하는 수동 정리 작업을 실행하여 프로세스를 용이하게 할 수 있습니다.

SQL Server 및 Azure SQL Managed Instance의 경우 기본값인 30분보다 짧은 간격으로 sp_flush_CT_internal_table_on_demand를 사용하여 백그라운드 작업을 만듭니다. Azure SQL 데이터베이스의 경우 Azure Logic Apps를 사용하여 이러한 작업을 예약할 수 있습니다.

다음 T-SQL 코드를 사용하여 변경 내용 추적을 위해 사이드 테이블 정리에 도움이 되는 작업을 만들 수 있습니다.

-- Loop to invoke manual cleanup procedure for cleaning up change tracking tables in a database
-- Fetch the tables enabled for change tracking
SELECT IDENTITY(INT, 1, 1) AS TableID,
    (SCHEMA_NAME(tbl.Schema_ID) + '.' + OBJECT_NAME(ctt.object_id)) AS TableName
INTO #CT_Tables
FROM sys.change_tracking_tables ctt
INNER JOIN sys.tables tbl
    ON tbl.object_id = ctt.object_id;

-- Set up the variables
DECLARE @start INT = 1,
    @end INT = (
        SELECT COUNT(*)
        FROM #CT_Tables
        ),
    @tablename VARCHAR(255);

WHILE (@start <= @end)
BEGIN
    -- Fetch the table to be cleaned up
    SELECT @tablename = TableName
    FROM #CT_Tables
    WHERE TableID = @start

    -- Execute the manual cleanup stored procedure
    EXEC sp_flush_CT_internal_table_on_demand @tablename

    -- Increment the counter
    SET @start = @start + 1;
END

DROP TABLE #CT_Tables;

다음을 통해 공유

변경 내용 추적 자동 정리 문제 해결

증상

디버깅 및 완화

자동 정리 상태

1. 정리가 꺼져 있음

2. 정리가 켜져 있지만 실행되지 않음

자동 정리가 실행되지만 진행되지 않음

1. 자동 정리 백로그 평가

2. 테이블 잠금 충돌 확인

3. 다른 원인 확인

syscommittab 문제 해결

정리 중 높은 CPU 사용률

30분보다 더 자주 정리 실행

피드백

추가 리소스

다음을 통해 공유

변경 내용 추적 자동 정리 문제 해결

증상

디버깅 및 완화

자동 정리 상태

1. 정리가 꺼져 있음

2. 정리가 켜져 있지만 실행되지 않음

자동 정리가 실행되지만 진행되지 않음

1. 자동 정리 백로그 평가

2. 테이블 잠금 충돌 확인

3. 다른 원인 확인

syscommittab 문제 해결

정리 중 높은 CPU 사용률

30분보다 더 자주 정리 실행

관련 콘텐츠

피드백

추가 리소스