꽉 찬 트랜잭션 로그 문제 해결(SQL Server 오류 9002)

적용 대상:SQL Server

이 문서는 SQL Server 인스턴스에 적용됩니다.

옵션 1: Azure Data Studio를 통해 실행 가능한 Notebook에서 직접 단계 실행

이 Notebook을 열기 전에 로컬 컴퓨터에 Azure Data Studio가 설치되어 있는지 확인합니다. 설치하려면 Azure Data Studio 다운로드 및 설치로 이동합니다.

옵션 2: 수동으로 단계 수행

이 문서에서는 전체 트랜잭션 로그에 대한 가능한 응답에 대해 설명하고 나중에 이를 방지하는 방법을 제안합니다.

트랜잭션 로그가 꽉 차면 SQL Server 데이터베이스 엔진에서 9002 오류가 발생합니다. 로그는 데이터베이스가 온라인 상태이거나 복구 중일 때 꽉 찰 수 있습니다. 데이터베이스가 온라인일 때 로그가 채워지면 데이터베이스는 온라인 상태를 유지하지만, 읽기만 가능하고 업데이트할 수 없습니다. 복구 중에 로그가 꽉 차면 데이터베이스 엔진은 데이터베이스를 RESOURCE PENDING로 표시합니다. 두 경우 모두 로그 공간을 사용할 수 있도록 하려면 사용자 작업이 필요합니다.

트랜잭션 로그가 가득 차는 일반적인 이유

가득 찬 트랜잭션 로그에 대한 적절한 응답은 로그가 꽉 차게 만든 조건에 따라 달라집니다. 일반적인 원인은 다음과 같습니다.

• 로그가 잘리지 않음
• 디스크 볼륨이 가득 참
• 로그 크기가 고정 최대값으로 설정되어 있거나 자동 증가가 사용하지 않도록 설정되어 있음
• 완료할 수 없는 복제 또는 가용성 그룹 동기화

다음의 특정 단계를 따르면 전체 트랜잭션 로그의 원인을 찾고 문제를 해결하는 데 도움이 됩니다.

• 로그 자르기
• 꽉 찬 디스크 볼륨 확인
• 로그 크기 제한 변경 또는 자동 증가 사용

1. 로그 자르기

이 문제에 대한 일반적인 해결 방법은 데이터베이스에 대해 트랜잭션 로그 백업이 수행되도록 하여 로그가 잘리도록 하는 것입니다. 가득 찬 트랜잭션 로그가 있는 데이터베이스에 대한 최근 트랜잭션 로그 기록이 표시되지 않는 경우 문제에 대한 해결 방법은 간단합니다. 데이터베이스의 일반 트랜잭션 로그 백업을 다시 시작합니다.

자세한 내용은 트랜잭션 로그 파일의 크기 관리 및 파일 축소를 검토하세요.

로그 잘림 설명

트랜잭션 로그를 자르는 것과 트랜잭션 로그를 축소하는 것은 차이가 있습니다. 로그 잘림은 일반적으로 트랜잭션 로그 백업 중에 발생하며 로그 내에서 커밋된 레코드를 제거하는 논리적 작업인 데 반해, 로그 축소는 파일 크기를 줄여 파일 시스템의 물리적 공간을 회수합니다. 로그 잘림은 VLF(가상 로그 파일) 경계에서 발생하며 로그 파일에는 많은 VLF가 포함될 수 있습니다. 로그 파일 내에 회수할 빈 공간이 있는 경우에만 로그 파일을 축소할 수 있습니다. 로그 파일만 축소해서는 전체 로그 파일의 문제를 해결할 수 없습니다. 대신 로그 파일이 가득 차서 잘리지 않는 이유를 찾아야 합니다.

로그 잘림을 방지하는 것은 무엇인가요?

특정 사례에서 로그 잘림을 방해하는 요소를 찾으려면 log_reuse_wait 카탈로그 뷰의 log_reuse_wait_desc 열과 sys.databases 열을 사용합니다. 자세한 내용은 sys.databases를 참조하십시오. 로그 잘림을 지연시킬 수 있는 요인에 대한 설명은 트랜잭션 로그를 참조하세요.

다음 T-SQL 명령 세트는 데이터베이스 트랜잭션 로그가 잘리지 않는지와 그 이유를 파악하는 데 도움이 됩니다. 다음 스크립트는 문제를 해결하는 단계도 권장합니다.

SET NOCOUNT ON;

DECLARE
    @SQL AS VARCHAR (8000),
    @log_reuse_wait AS TINYINT,
    @log_reuse_wait_desc AS NVARCHAR (120),
    @dbname AS SYSNAME,
    @database_id AS INT,
    @recovery_model_desc AS VARCHAR (24);

IF (OBJECT_id(N'tempdb..#CannotTruncateLog_Db') IS NOT NULL)
    BEGIN
        DROP TABLE #CannotTruncateLog_Db;
    END

--get info about transaction logs in each database.
IF (OBJECT_id(N'tempdb..#dm_db_log_space_usage') IS NOT NULL)
    BEGIN
        DROP TABLE #dm_db_log_space_usage;
    END

SELECT *
INTO #dm_db_log_space_usage
FROM sys.dm_db_log_space_usage
WHERE 1 = 0;

DECLARE log_space CURSOR
    FOR SELECT NAME
        FROM sys.databases;

OPEN log_space;

FETCH NEXT FROM log_space INTO @dbname;

WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @SQL = '
            INSERT INTO #dm_db_log_space_usage (
                database_id,
                total_log_size_in_bytes,
                used_log_space_in_bytes,
                used_log_space_in_percent,
                log_space_in_bytes_since_last_backup
                )
            SELECT database_id,
                total_log_size_in_bytes,
                used_log_space_in_bytes,
                used_log_space_in_percent,
                log_space_in_bytes_since_last_backup
            FROM ' + QUOTENAME(@dbname) + '.sys.dm_db_log_space_usage;';
        BEGIN TRY
            EXECUTE (@SQL);
        END TRY
        BEGIN CATCH
            SELECT ERROR_MESSAGE() AS ErrorMessage;
        END CATCH
        FETCH NEXT FROM log_space INTO @dbname;
    END

CLOSE log_space;
DEALLOCATE log_space;

--select the affected databases
SELECT
    sdb.name AS DbName,
    sdb.log_reuse_wait,
    sdb.log_reuse_wait_desc,
    CASE
        WHEN log_reuse_wait = 1 THEN 'No checkpoint has occurred since the last log truncation, or the head of the log has not yet moved beyond'
        WHEN log_reuse_wait = 2 THEN 'A log backup is required before the transaction log can be truncated.'
        WHEN log_reuse_wait = 3 THEN 'A data backup or a restore is in progress (all recovery models). Please wait or cancel backup'
        WHEN log_reuse_wait = 4 THEN 'A long-running active transaction or a deferred transaction is keeping log from being truncated. You can attempt a log backup to free space or complete/rollback long transaction'
        WHEN log_reuse_wait = 5 THEN 'Database mirroring is paused, or under high-performance mode, the mirror database is significantly behind the principal database. (Full recovery model only)'
        WHEN log_reuse_wait = 6 THEN 'During transactional replication, transactions relevant to the publications are still undelivered to the distribution database. Investigate the status of agents involved in replication or Changed Data Capture (CDC). (Full recovery model only.)'
        WHEN log_reuse_wait = 7 THEN 'A database snapshot is being created. This is a routine, and typically brief, cause of delayed log truncation.'
        WHEN log_reuse_wait = 8 THEN 'A transaction log scan is occurring. This is a routine, and typically a brief cause of delayed log truncation.'
        WHEN log_reuse_wait = 9 THEN 'A secondary replica of an availability group is applying transaction log records of this database to a corresponding secondary database. (Full recovery model only.)'
        WHEN log_reuse_wait = 13 THEN 'If a database is configured to use indirect checkpoints, the oldest page on the database might be older than the checkpoint log sequence number (LSN).'
        WHEN log_reuse_wait = 16 THEN 'An In-Memory OLTP checkpoint has not occurred since the last log truncation, or the head of the log has not yet moved beyond a VLF.'
        ELSE 'None'
    END AS log_reuse_wait_explanation,
    sdb.database_id,
    sdb.recovery_model_desc,
    lsu.used_log_space_in_bytes / 1024 AS Used_log_size_MB,
    lsu.total_log_size_in_bytes / 1024 AS Total_log_size_MB,
    100 - lsu.used_log_space_in_percent AS Percent_Free_Space
INTO #CannotTruncateLog_Db
FROM sys.databases AS sdb
     INNER JOIN #dm_db_log_space_usage AS lsu
         ON sdb.database_id = lsu.database_id
WHERE log_reuse_wait > 0;

SELECT * FROM #CannotTruncateLog_Db;

DECLARE no_truncate_db CURSOR FOR
SELECT
    log_reuse_wait,
    log_reuse_wait_desc,
    DbName,
    database_id,
    recovery_model_desc
FROM #CannotTruncateLog_Db;

OPEN no_truncate_db;

FETCH NEXT FROM no_truncate_db
INTO
    @log_reuse_wait,
    @log_reuse_wait_desc,
    @dbname,
    @database_id,
    @recovery_model_desc;

WHILE @@FETCH_STATUS = 0
    BEGIN
        IF (@log_reuse_wait > 0)
        BEGIN
            SELECT '-- ' + QUOTENAME(@dbname) + ' database has log_reuse_wait = ' + @log_reuse_wait_desc + ' --' AS 'Individual Database Report';
        END
        IF (@log_reuse_wait = 1)
        BEGIN
            SELECT 'Consider running the checkpoint command to attempt resolving this issue or further t-shooting may be required on the checkpoint process. Also, examine the log for active VLFs at the end of file' AS Recommendation;
            SELECT 'USE ' + QUOTENAME(@dbname) + '; CHECKPOINT' AS CheckpointCommand;
            SELECT 'SELECT * FROM sys.dm_db_log_info(' + CONVERT (VARCHAR, @database_id) + ')' AS VLF_LogInfo;
        END
        ELSE IF (@log_reuse_wait = 2)
        BEGIN
            SELECT 'Is ' + @recovery_model_desc + ' recovery model the intended choice for ' + QUOTENAME(@dbname) + ' database? Review recovery models and determine if you need to change it. https://learn.microsoft.com/sql/relational-databases/backup-restore/recovery-models-sql-server' AS RecoveryModelChoice;
            SELECT 'To truncate the log consider performing a transaction log backup on database ' + QUOTENAME(@dbname) + ' which is in ' + @recovery_model_desc + ' recovery model. Be mindful of any existing log backup chains that could be broken' AS Recommendation;
            SELECT 'BACKUP LOG ' + QUOTENAME(@dbname) + ' TO DISK = ''some_volume:\some_folder\' + QUOTENAME(@dbname) + '_LOG.trn '';' AS BackupLogCommand;
        END
        ELSE IF (@log_reuse_wait = 3)
        BEGIN
            SELECT 'Either wait for or cancel any active backups currently running for database ' + QUOTENAME(@dbname) + '. To check for backups, run this command:' AS Recommendation;
            SELECT 'SELECT * FROM sys.dm_exec_requests WHERE command LIKE ''backup%'' OR command LIKE ''restore%''' AS FindBackupOrRestore;
        END
        ELSE IF (@log_reuse_wait = 4)
        BEGIN
            SELECT 'Active transactions currently running for database ' + QUOTENAME(@dbname) + '. To check for active transactions, run these commands:' AS Recommendation;
            SELECT 'DBCC OPENTRAN (' + QUOTENAME(@dbname) + ')' AS FindOpenTran;
            SELECT 'SELECT database_id, db_name(database_id) AS dbname, database_transaction_begin_time, database_transaction_state, database_transaction_log_record_count, database_transaction_log_bytes_used, database_transaction_begin_lsn, stran.session_id FROM sys.dm_tran_database_transactions dbtran LEFT OUTER JOIN sys.dm_tran_session_transactions stran ON dbtran.transaction_id = stran.transaction_id WHERE database_id = ' + CONVERT (VARCHAR, @database_id) AS FindOpenTransAndSession;
        END
        ELSE IF (@log_reuse_wait = 5)
        BEGIN
            SELECT 'Database Mirroring for database ' + QUOTENAME(@dbname) + ' is behind on synchronization. To check the state of DBM, run the commands below:' AS Recommendation;
            SELECT 'SELECT db_name(database_id), mirroring_state_desc, mirroring_role_desc, mirroring_safety_level_desc FROM sys.database_mirroring WHERE mirroring_guid IS NOT NULL and mirroring_state <> 4 AND database_id = ' + CONVERT (sysname, @database_id) AS CheckMirroringStatus;
            SELECT 'Database Mirroring for database ' + QUOTENAME(@dbname) + ' may be behind: check unsent_log, send_rate, unrestored_log, recovery_rate, average_delay in this output' AS Recommendation;
            SELECT 'EXECUTE msdb.sys.sp_dbmmonitoraddmonitoring 1; EXECUTE msdb.sys.sp_dbmmonitorresults ' + QUOTENAME(@dbname) + ', 5, 0; WAITFOR DELAY ''00:01:01''; EXECUTE msdb.sys.sp_dbmmonitorresults ' + QUOTENAME(@dbname) + '; EXECUTE msdb.sys.sp_dbmmonitordropmonitoring' AS CheckMirroringStatusAnd;
        END
        ELSE IF (@log_reuse_wait = 6)
        BEGIN
            SELECT 'Replication transactions still undelivered FROM publisher database ' + QUOTENAME(@dbname) + ' to Distribution database. Check the oldest non-distributed replication transaction. Also check if the Log Reader Agent is running and if it has encountered any errors' AS Recommendation;
            SELECT 'DBCC OPENTRAN  (' + QUOTENAME(@dbname) + ')' AS CheckOldestNonDistributedTran;
            SELECT 'SELECT top 5 * FROM distribution..MSlogreader_history WHERE runstatus in (6, 5) OR error_id <> 0 AND agent_id = find_in_mslogreader_agents_table ORDER BY time desc ' AS LogReaderAgentState;
        END
        ELSE IF (@log_reuse_wait = 9)
        BEGIN
            SELECT 'Always On transactions still undelivered FROM primary database ' + QUOTENAME(@dbname) + ' to Secondary replicas. Check the Health of AG nodes and if there is latency is Log block movement to Secondaries' AS Recommendation;
            SELECT 'SELECT availability_group = CAST(ag.name AS VARCHAR(30)), primary_replica = CAST(ags.primary_replica AS VARCHAR(30)), primary_recovery_health_desc = CAST(ags.primary_recovery_health_desc AS VARCHAR(30)), synchronization_health_desc = CAST(ags.synchronization_health_desc AS VARCHAR(30)), ag.failure_condition_level, ag.health_check_timeout, automated_backup_preference_desc = CAST(ag.automated_backup_preference_desc AS VARCHAR(10)) FROM sys.availability_groups ag join sys.dm_hadr_availability_group_states ags on ag.group_id=ags.group_id' AS CheckAGHealth;
            SELECT 'SELECT  group_name = CAST(arc.group_name AS VARCHAR(30)), replica_server_name = CAST(arc.replica_server_name AS VARCHAR(30)), node_name = CAST(arc.node_name AS VARCHAR(30)), role_desc = CAST(ars.role_desc AS VARCHAR(30)), ar.availability_mode_Desc, operational_state_desc = CAST(ars.operational_state_desc AS VARCHAR(30)), connected_state_desc = CAST(ars.connected_state_desc AS VARCHAR(30)), recovery_health_desc = CAST(ars.recovery_health_desc AS VARCHAR(30)), synchronization_health_desc = CAST(ars.synchronization_health_desc AS VARCHAR(30)), ars.last_connect_error_number, last_connect_error_description = CAST(ars.last_connect_error_description AS VARCHAR(30)), ars.last_connect_error_timestamp, primary_role_allow_connections_desc = CAST(ar.primary_role_allow_connections_desc AS VARCHAR(30)) FROM sys.dm_hadr_availability_replica_cluster_nodes arc join sys.dm_hadr_availability_replica_cluster_states arcs on arc.replica_server_name=arcs.replica_server_name join sys.dm_hadr_availability_replica_states ars on arcs.replica_id=ars.replica_id join sys.availability_replicas ar on ars.replica_id=ar.replica_id join sys.availability_groups ag on ag.group_id = arcs.group_id and ag.name = arc.group_name ORDER BY CAST(arc.group_name AS VARCHAR(30)), CAST(ars.role_desc AS VARCHAR(30))' AS CheckReplicaHealth;
            SELECT 'SELECT database_name = CAST(drcs.database_name AS VARCHAR(30)), drs.database_id, drs.group_id, drs.replica_id, drs.is_local, drcs.is_failover_ready, drcs.is_pending_secondary_suspend, drcs.is_database_joined, drs.is_suspended, drs.is_commit_participant, suspend_reason_desc = CAST(drs.suspend_reason_desc AS VARCHAR(30)), synchronization_state_desc = CAST(drs.synchronization_state_desc AS VARCHAR(30)), synchronization_health_desc = CAST(drs.synchronization_health_desc AS VARCHAR(30)), database_state_desc = CAST(drs.database_state_desc AS VARCHAR(30)), drs.last_sent_lsn, drs.last_sent_time, drs.last_received_lsn, drs.last_received_time, drs.last_hardened_lsn, drs.last_hardened_time, drs.last_redone_lsn, drs.last_redone_time, drs.log_send_queue_size, drs.log_send_rate, drs.redo_queue_size, drs.redo_rate, drs.filestream_send_rate, drs.end_of_log_lsn, drs.last_commit_lsn, drs.last_commit_time, drs.low_water_mark_for_ghosts, drs.recovery_lsn, drs.truncation_lsn, pr.file_id, pr.error_type, pr.page_id, pr.page_status, pr.modification_time FROM sys.dm_hadr_database_replica_cluster_states drcs join sys.dm_hadr_database_replica_states drs on drcs.replica_id=drs.replica_id and drcs.group_database_id=drs.group_database_id left outer join sys.dm_hadr_auto_page_repair pr on drs.database_id=pr.database_id  order by drs.database_id' AS LogMovementHealth;
            SELECT 'For more information see https://learn.microsoft.com/troubleshoot/sql/availability-groups/error-9002-transaction-log-large' AS OnlineDOCResource;
        END
        ELSE IF (@log_reuse_wait IN (10, 11, 12, 14))
        BEGIN
            SELECT 'This state is not documented and is expected to be rare and short-lived' AS Recommendation;
        END
        ELSE IF (@log_reuse_wait = 13)
        BEGIN
            SELECT 'The oldest page on the database might be older than the checkpoint log sequence number (LSN). In this case, the oldest page can delay log truncation.' AS Finding;
            SELECT 'This state should be short-lived, but if you find it is taking a long time, you can consider disabling Indirect Checkpoint temporarily' AS Recommendation;
            SELECT 'ALTER DATABASE ' + QUOTENAME(@dbname) + ' SET TARGET_RECOVERY_TIME = 0 SECONDS;' AS DisableIndirectCheckpointTemporarily;
        END
        ELSE IF (@log_reuse_wait = 16)
        BEGIN
            SELECT 'For memory-optimized tables, an automatic checkpoint is taken when transaction log file becomes bigger than 1.5 GB since the last checkpoint (includes both disk-based and memory-optimized tables)' AS Finding;
            SELECT 'Review https://learn.microsoft.com/archive/blogs/sqlcat/logging-and-checkpoint-process-for-memory-optimized-tables-2' AS ReviewBlog;
            SELECT 'USE ' + QUOTENAME(@dbname) + '; CHECKPOINT;' AS RunCheckpoint;
        END
        FETCH NEXT FROM no_truncate_db INTO
            @log_reuse_wait,
            @log_reuse_wait_desc,
            @dbname,
            @database_id,
            @recovery_model_desc;
    END

CLOSE no_truncate_db;
DEALLOCATE no_truncate_db;

LOG_BACKUP log_reuse_wait

데이터베이스 복구 모델을 보거나 LOG_BACKUPlog_reuse_wait 검토하는 경우 고려해야 할 가장 일반적인 작업은 데이터베이스의 트랜잭션 로그를 백업하는 것입니다.

데이터베이스의 복구 모델 고려

트랜잭션 로그가 또는 LOG_BACKUP category로 log_reuse_wait 잘리지 않을 수 있는데, 이는 백업한 적이 없기 때문입니다. 이러한 대부분의 경우 데이터베이스는 OR FULL 복구 모델을 사용 BULK_LOGGED 하지만 트랜잭션 로그를 백업하지 않았습니다. 각 데이터베이스 복구 모델을 신중하게 고려해야 하며, 오류 9002의 발생을 최소화하기 위해 복구 FULL 모델의 모든 데이터베이스에서 BULK_LOGGED 정기적인 트랜잭션 로그 백업을 수행해야 합니다. 자세한 내용은 복구 모델을 참조하세요.

로그 백업

FULL 또는 BULK_LOGGED 복구 모델에서 트랜잭션 로그가 최근에 백업되지 않은 경우 백업이 로그 잘림을 방지하는 것일 수 있습니다. 로그 레코드를 해제하고 로그가 잘리도록 하려면 트랜잭션 로그를 백업해야 합니다. 로그가 백업된 적이 없으면 데이터베이스 엔진에서 로그를 마지막 백업 시점까지 자를 수 있도록 두 개의 로그 백업을 만들어야 합니다. 로그를 자르면 새 로그 레코드를 위한 논리 공간이 늘어납니다. 로그가 다시 꽉 차지 않게 하려면 정기적으로 더 자주 로그 백업을 수행합니다. 자세한 내용은 복구 모델을 참조하세요.

서버 인스턴스에서 이루어지는 모든 SQL Server 백업 및 복원 작업의 전체 기록은 msdb 시스템 데이터베이스에 저장됩니다. 데이터베이스의 전체 백업 기록을 검토하려면 다음 샘플 스크립트를 사용합니다.

SELECT bs.database_name,
    CASE
        WHEN bs.type = 'D' AND bs.is_copy_only = 0 THEN 'Full Database'
        WHEN bs.type = 'D' AND bs.is_copy_only = 1 THEN 'Full Copy-Only Database'
        WHEN bs.type = 'I' THEN 'Differential database backup'
        WHEN bs.type = 'L' THEN 'Transaction Log'
        WHEN bs.type = 'F' THEN 'File or filegroup'
        WHEN bs.type = 'G' THEN 'Differential file'
        WHEN bs.type = 'P' THEN 'Partial'
        WHEN bs.type = 'Q' THEN 'Differential partial'
    END + ' Backup' AS backuptype,
    bs.recovery_model,
    bs.Backup_Start_Date AS BackupStartDate,
    bs.Backup_Finish_Date AS BackupFinishDate,
    bf.physical_device_name AS LatestBackupLocation,
    bs.backup_size / 1024. / 1024. AS backup_size_mb,
    bs.compressed_backup_size / 1024. / 1024. AS compressed_backup_size_mb,
    database_backup_lsn, -- For tlog and differential backups, this is the checkpoint_lsn of the FULL backup it is based on.
    checkpoint_lsn,
    begins_log_chain
FROM msdb.dbo.backupset AS bs
     LEFT OUTER JOIN msdb.dbo.backupmediafamily AS bf
         ON bs.[media_set_id] = bf.[media_set_id]
WHERE recovery_model IN ('FULL', 'BULK-LOGGED')
      AND bs.backup_start_date > DATEADD(month, -2, SYSDATETIME()) --only look at last two months
ORDER BY bs.database_name ASC, bs.Backup_Start_Date DESC;

서버 인스턴스에서 이루어지는 모든 SQL Server 백업 및 복원 작업의 전체 기록은 msdb 시스템 데이터베이스에 저장됩니다. 백업 기록에 대한 자세한 내용은 백업 기록 및 헤더 정보(SQL Server)를 참조하세요.

트랜잭션 로그 백업 만들기

로그를 백업하는 방법의 예:

BACKUP LOG [dbname] TO DISK = 'some_volume:\some_folder\dbname_LOG.trn';

• 트랜잭션 로그 백업

• SqlBackup(SMO)

활성_트랜잭션 log_재사용_대기

이유 문제를 해결하는 ACTIVE_TRANSACTION 단계에는 장기 실행 트랜잭션을 검색하고 해결하는 것이 포함됩니다(경우에 따라 명령을 사용하여 해결).KILL

장기 실행 트랜잭션 검색

장기 실행 트랜잭션으로 인해 트랜잭션 로그가 꽉 찰 수 있습니다. 장기 실행 트랜잭션을 찾으려면 다음 옵션 중 하나를 사용합니다.

• sys.dm_tran_database_transactions:

  이 동적 관리 뷰는 데이터베이스 수준에서 트랜잭션 정보를 반환합니다. 장기 실행 트랜잭션의 경우 특히 관심 있는 열에는 첫 번째 로그 레코드의 시간(),database_transaction_begin_time 트랜잭션의 현재 상태(),database_transaction_state 트랜잭션 로그()에 있는 레코드의 BEGINdatabase_transaction_begin_lsn이 포함됩니다.

• DBCC 오픈트란:

  이 문을 사용하면 트랜잭션 소유자의 사용자 ID를 식별할 수 있으므로 잠재적으로 트랜잭션의 출처를 추적하여 보다 질서 있게 종료할 수 있습니다(롤백 대신 커밋).

트랜잭션 중지

경우에 따라 트랜잭션을 종료해야 하는 경우도 있습니다. KILL 문을 사용해야 할 수도 있습니다. 특히 종료하고 싶지 않은 중요한 프로세스가 실행 중인 경우 매우 주의해서 이 문장을 KILL 사용합니다.

체크포인트 log_reuse_wait

마지막 로그 잘림 이후 검사점이 발생하지 않았거나 모든 복구 모델에서 로그의 헤드가 아직 VLF(가상 로그 파일)를 벗어나지 않았습니다.

이는 로그 잘림을 지연시키는 일반적인 이유입니다. 지연된 경우 데이터베이스에서 CHECKPOINT 명령을 실행하거나 로그 VLF를 검사하는 것이 좋습니다.

USE dbname;
CHECKPOINT;

SELECT * FROM sys.dm_db_log_info(db_id('dbname'));

사용 가능 복제본 로그 재사용 대기 (AVAILABILITY_REPLICA log_reuse_wait)

Always On 가용성 그룹 주 복제본의 트랜잭션 변경 내용이 보조 복제본에서 아직 확정되지 않은 경우 주 복제본 트랜잭션 로그은 자를 수 없습니다. 이로 인해 로그가 증가할 수 있으며, 보조 복제본이 동기 커밋 모드로 설정되어 있거나 비동기 커밋 모드로 설정되어 있는지에 상관없이 발생할 수 있습니다. 이러한 유형의 문제를 해결하는 방법에 대한 자세한 내용은 오류 9002. AVAILABILITY_REPLICA 오류로 인해 데이터베이스 트랜잭션 로그가 가득 참을 참조하세요.

복제, 변경 내용 추적 또는 CDC

복제, 변경 내용 추적 및 CDC(변경 데이터 캡처)와 같은 기능은 트랜잭션 로그를 사용하므로 트랜잭션 또는 변경 내용이 전달되지 않으면 트랜잭션 로그의 잘림을 방해할 수도 있습니다.

DBCC OPENTRAN, 복제 모니터 또는 변경 내용 추적을 위한 저장 프로시저 및 CDC를 사용하여 이러한 기능의 문제를 조사하고 해결하세요.

log_reuse_wait 요인에 대한 정보 찾기

자세한 내용은 로그 잘림을 지연시킬 수 있는 요소를 참조하세요.

2. 가득 찬 디스크 볼륨 해결

경우에 따라 트랜잭션 로그 파일을 호스트하는 디스크 볼륨이 가득 찰 수도 있습니다. 다음 작업 중 하나를 수행하여 가득 찬 디스크에서 발생하는 가득 찬 로그 시나리오를 해결할 수 있습니다.

사용 가능한 디스크 공간

다른 파일을 삭제하거나 이동하여 데이터베이스에 대한 트랜잭션 로그 파일이 포함된 디스크 드라이브의 디스크 공간을 확보할 수 있습니다. 해제된 디스크 공간을 사용하면 복구 시스템에서 로그 파일을 자동으로 확대할 수 있습니다.

로그 파일을 다른 디스크로 이동

현재 로그 파일이 포함된 드라이브의 디스크 공간을 충분히 확보할 수 없는 경우 충분한 공간이 있는 다른 드라이브로 파일을 이동하는 것이 좋습니다.

로그 파일의 위치를 변경하는 방법에 대한 자세한 내용은 데이터베이스 파일 이동을 참조하십시오.

다른 디스크에 로그 파일 추가

ALTER DATABASE <database_name> ADD LOG FILE을 사용하여 충분한 공간이 있는 다른 디스크의 데이터베이스에 새 로그 파일을 추가합니다. 단일 데이터베이스에 대한 여러 로그 파일은 장기 조건이 아닌 공간 문제를 해결하기 위한 임시 조건으로 간주되어야 합니다. 대부분의 데이터베이스에는 하나의 트랜잭션 로그 파일만 있어야 합니다. 트랜잭션 로그가 가득 차서 잘리지 못하는 이유를 계속 조사합니다. 추가 임시 트랜잭션 로그 파일은 고급 문제 해결 단계로만 추가하는 것이 좋습니다.

자세한 내용은 데이터베이스에 데이터 또는 로그 파일 추가을 참조하세요.

권장 작업에 대한 유틸리티 스크립트

이 단계는 디스크 공간의 큰 비율을 사용하는 로그 파일을 파악하고 작업을 제안하는 다음 T-SQL 스크립트를 실행하여 부분적으로 자동화할 수 있습니다.

DECLARE @log_reached_disk_size AS BIT = 0;

SELECT [name] AS LogName,
       physical_name,
       CONVERT (BIGINT, size) * 8 / 1024 AS LogFile_Size_MB,
       volume_mount_point,
       available_bytes / 1024 / 1024 AS Available_Disk_space_MB,
       (CONVERT (BIGINT, size) * 8.0 / 1024) / (available_bytes / 1024 / 1024) * 100 AS file_size_as_percentage_of_disk_space,
       db_name(mf.database_id) AS DbName
FROM sys.master_files AS mf
CROSS APPLY sys.dm_os_volume_stats(mf.database_id, file_id)
WHERE mf.[type_desc] = 'LOG'
    AND (CONVERT (BIGINT, size) * 8.0 / 1024) / (available_bytes / 1024 / 1024) * 100 > 90 --log is 90% of disk drive
ORDER BY size DESC;

IF @@ROWCOUNT > 0
    BEGIN
        SET @log_reached_disk_size = 1;
        -- Discover if any logs have filled the volume they reside on, or are close to filling the volume.
        -- Either add a new file to a new drive, or shrink an existing file.
        -- If it cannot shrink, direct the script to recommend next steps.
        DECLARE @db_name_filled_disk AS sysname, @log_name_filled_disk AS sysname, @go_beyond_size AS BIGINT;
        DECLARE log_filled_disk CURSOR
            FOR SELECT db_name(mf.database_id),
                       name
                FROM sys.master_files AS mf
CROSS APPLY sys.dm_os_volume_stats(mf.database_id, file_id)
                WHERE mf.[type_desc] = 'LOG'
                      AND (CONVERT (BIGINT, size) * 8.0 / 1024) / (available_bytes / 1024 / 1024) * 100 > 90 --log is 90% of disk drive
                ORDER BY size DESC;
        OPEN log_filled_disk;
        FETCH NEXT FROM log_filled_disk INTO @db_name_filled_disk, @log_name_filled_disk;
        WHILE @@FETCH_STATUS = 0
            BEGIN
                SELECT 'Transaction log for database "' + @db_name_filled_disk + '" has nearly or completely filled disk volume it resides on!' AS Finding;
                SELECT 'Consider using one of the below commands to shrink the "' + @log_name_filled_disk + '" transaction log file size or add a new file to a NEW volume' AS Recommendation;
                SELECT 'DBCC SHRINKFILE(''' + @log_name_filled_disk + ''')' AS Shrinkfile_Command;
                SELECT 'ALTER DATABASE ' + @db_name_filled_disk + ' ADD LOG FILE ( NAME = N''' + @log_name_filled_disk + '_new'', FILENAME = N''NEW_VOLUME_AND_FOLDER_LOCATION\' + @log_name_filled_disk + '_NEW.LDF'', SIZE = 81920KB , FILEGROWTH = 65536KB )' AS AddNewFile;
                SELECT 'If shrink does not reduce the file size, likely it is because it has not been truncated. Please review next section below. See https://learn.microsoft.com/sql/t-sql/database-console-commands/dbcc-shrinkfile-transact-sql' AS TruncateFirst;
                SELECT 'Can you free some disk space on this volume? If so, do this to allow for the log to continue growing when needed.' AS FreeDiskSpace;
                FETCH NEXT FROM log_filled_disk INTO @db_name_filled_disk, @log_name_filled_disk;
            END
        CLOSE log_filled_disk;
        DEALLOCATE log_filled_disk;
    END

3. 로그 크기 제한 변경 또는 자동 증가 활성화

트랜잭션 로그 크기가 상한으로 설정되거나 자동 증가 기능이 허용되지 않는 경우 오류 9002가 생성될 수 있습니다. 이 경우 자동 증가를 사용하도록 설정하거나 수동으로 로그 크기를 늘리면 문제가 해결될 수 있습니다. 이 T-SQL 명령을 사용하여 이러한 로그 파일을 찾고 제공된 권장 사항을 따릅니다.

SELECT DB_NAME(database_id) AS DbName,
       name AS LogName,
       physical_name,
       type_desc,
       CONVERT (BIGINT, SIZE) * 8 / 1024 AS LogFile_Size_MB,
       CONVERT (BIGINT, max_size) * 8 / 1024 AS LogFile_MaxSize_MB,
       (SIZE * 8.0 / 1024) / (max_size * 8.0 / 1024) * 100 AS percent_full_of_max_size,
       CASE WHEN growth = 0 THEN 'AUTOGROW_DISABLED' ELSE 'Autogrow_Enabled' END AS AutoGrow
FROM sys.master_files
WHERE file_id = 2
      AND (SIZE * 8.0 / 1024) / (max_size * 8.0 / 1024) * 100 > 90
      AND max_size NOT IN (-1, 268435456)
      OR growth = 0;

IF @@ROWCOUNT > 0
    BEGIN
        DECLARE @db_name_max_size AS sysname, @log_name_max_size AS sysname, @configured_max_log_boundary AS BIGINT, @auto_grow AS INT;
        DECLARE reached_max_size CURSOR
        FOR SELECT db_name(database_id),
                    name,
                    CONVERT (BIGINT, SIZE) * 8 / 1024,
                    growth
            FROM sys.master_files
            WHERE file_id = 2
                AND ((SIZE * 8.0 / 1024) / (max_size * 8.0 / 1024) * 100 > 90
                    AND max_size NOT IN (-1, 268435456)
                    OR growth = 0);
        OPEN reached_max_size;
        FETCH NEXT FROM reached_max_size INTO @db_name_max_size, @log_name_max_size, @configured_max_log_boundary, @auto_grow;
        WHILE @@FETCH_STATUS = 0
        BEGIN
            IF @auto_grow = 0
                BEGIN
                    SELECT 'The database "' + @db_name_max_size + '" contains a log file "' + @log_name_max_size + '" whose autogrow has been DISABLED' AS Finding;
                    SELECT 'Consider enabling autogrow or increasing file size via these ALTER DATABASE commands' AS Recommendation;
                    SELECT 'ALTER DATABASE ' + @db_name_max_size + ' MODIFY FILE ( NAME = N''' + @log_name_max_size + ''', FILEGROWTH = 65536KB)' AS AutoGrowth;
                END
            ELSE
                BEGIN
                    SELECT 'The database "' + @db_name_max_size + '" contains a log file "' + @log_name_max_size + '" whose max limit is set to ' + CONVERT (VARCHAR (24), @configured_max_log_boundary) + ' MB and this limit has been reached!' AS Finding;
                    SELECT 'Consider using one of the below ALTER DATABASE commands to either change the log file size or add a new file' AS Recommendation;
                END
            SELECT 'ALTER DATABASE ' + @db_name_max_size + ' MODIFY FILE ( NAME = N''' + @log_name_max_size + ''', MAXSIZE = UNLIMITED)' AS UnlimitedSize;
            SELECT 'ALTER DATABASE ' + @db_name_max_size + ' MODIFY FILE ( NAME = N''' + @log_name_max_size + ''', MAXSIZE = something_larger_than_' + CONVERT (VARCHAR (24), @configured_max_log_boundary) + 'MB )' AS IncreasedSize;
            SELECT 'ALTER DATABASE ' + @db_name_max_size + ' ADD LOG FILE ( NAME = N''' + @log_name_max_size + '_new'', FILENAME = N''SOME_FOLDER_LOCATION\' + @log_name_max_size + '_NEW.LDF'', SIZE = 81920KB , FILEGROWTH = 65536KB )' AS AddNewFile;
            FETCH NEXT FROM reached_max_size INTO @db_name_max_size, @log_name_max_size, @configured_max_log_boundary, @auto_grow;
        END
        CLOSE reached_max_size;
        DEALLOCATE reached_max_size;
    END
ELSE
    SELECT 'Found no files that have reached max log file size' AS Findings;

로그 파일 크기 늘리기 또는 자동 증가 사용

로그 디스크에 사용 가능한 공간이 있으면 로그 파일의 크기를 늘릴 수 있습니다. 로그 파일의 최대 크기는 로그 파일당 2TB입니다.

자동 증가가 사용하지 않도록 설정되어 있고 데이터베이스가 온라인 상태이며 디스크에 충분한 공간이 있는 경우 다음과 같은 조치를 고려합니다.

• 수동으로 파일 크기를 늘려 단일 증분을 생성합니다. 이는 로그 크기 증가 및 크기에 대한 일반적인 권장 사항입니다.

• 옵션을 위해 0이 아닌 증가 증분 ALTER DATABASE 을 FILEGROWTH 설정하는 문을 사용하여 자동 증가를 설정합니다. SQL Server의 자동 증가 및 자동 축소 설정에 대한 고려 사항을 참조하세요.

관련 콘텐츠

• ALTER DATABASE(Transact-SQL)
• 트랜잭션 로그 파일의 크기 관리
• 트랜잭션 로그 백업(SQL Server)
• sp_add_log_file_recover_suspect_db(Transact-SQL)
• MSSQLSERVER_9002
• 로그 파일 구조가 데이터베이스 복구 시간에 미치는 영향 - Microsoft Tech Community