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

적용 대상:SQL Server

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

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

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

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

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

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

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

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

전체 트랜잭션 로그를 확인하는 방법

다음 특정 단계는 가득 찬 트랜잭션 로그에 대한 이유를 찾고 문제를 해결하는 데 도움이 됩니다.

1. 로그 잘라내기

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

로그 잘림 설명

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

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

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

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

SET NOCOUNT ON
DECLARE @SQL VARCHAR (8000), @log_reuse_wait tinyint, @log_reuse_wait_desc nvarchar(120), @dbname sysname, @database_id int, @recovery_model_desc 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 ' + @dbname +'.sys.dm_db_log_space_usage'

    
    BEGIN TRY  
        exec (@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, 
    log_reuse_wait_explanation = 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 defferred 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,

    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 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)
        select '-- ''' + @dbname +  ''' database has log_reuse_wait = ' + @log_reuse_wait_desc + ' --'  as 'Individual Database Report'


    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 ''' + @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 ''' + @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 ''' + @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 [' + @dbname + '] TO DISK = ''some_volume:\some_folder\' + @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 ''' +@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 ''' +@dbname+ '''. To check for active transactions, run these commands:' as Recommendation
        select 'DBCC OPENTRAN (''' +@dbname+ ''')' as FindOpenTran
        select 'select database_id, db_name(database_id) 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 ''' +@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 ''' +@dbname+ ''' may be behind: check unsent_log, send_rate, unrestored_log, recovery_rate, average_delay in this output' as Recommendation
        select 'exec msdb.sys.sp_dbmmonitoraddmonitoring 1; exec msdb.sys.sp_dbmmonitorresults ''' + @dbname+ ''', 5, 0; waitfor delay ''00:01:01''; exec msdb.sys.sp_dbmmonitorresults ''' + @dbname+ '''; exec msdb.sys.sp_dbmmonitordropmonitoring'   as CheckMirroringStatusAnd
    END

    else if (@log_reuse_wait = 6)
    BEGIN
        select 'Replication transactions still undelivered from publisher database ''' +@dbname+ ''' to Distribution database. Check the oldest non-distributed replication transaction. Also check if the Log Reader Agent is running and if it has encoutered any errors' as Recommendation
        select 'DBCC OPENTRAN  (''' + @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 ''' +@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 [' +@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://blogs.msdn.microsoft.com/sqlcat/2016/05/20/logging-and-checkpoint-process-for-memory-optimized-tables-2/' as ReviewBlog
        select 'use ' +@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_BACKUP log_reuse_wait 범주로 트랜잭션 로그가 잘리지 못할 수 있습니다. 대부분의 경우 데이터베이스는 FULL 또는 BULK_LOGGED 복구 모델을 사용하지만 트랜잭션 로그를 백업하지 않았습니다. 각 데이터베이스 복구 모델을 신중하게 고려해야 합니다. FULL 또는 BULK LOGGED 복구 모델의 모든 데이터베이스에서 트랜잭션 로그 백업을 수행하여 오류 9002 발생을 최소화해야 합니다. 자세한 내용은 복구 모델을 참조하세요.

로그 백업

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

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

SELECT bs.database_name
, backuptype = 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'
, bs.recovery_model
, BackupStartDate = bs.Backup_Start_Date
, BackupFinishDate = bs.Backup_Finish_Date
, LatestBackupLocation = bf.physical_device_name
, backup_size_mb = bs.backup_size/1024./1024.
, compressed_backup_size_mb = bs.compressed_backup_size/1024./1024.
, 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'

• 트랜잭션 로그 백업(SQL Server)

• SqlBackup(SMO)

ACTIVE_TRANSACTION log_reuse_wait

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

장기 실행 트랜잭션 검색

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

• sys.dm_tran_database_transactions.

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

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

트랜잭션 중지

경우에 따라 트랜잭션을 종료해야 하는 경우도 있습니다. KILL 문을 사용해야 할 수도 있습니다. 특히 중지하지 않을 중요한 프로세스가 실행 중일 때는 이 문을 신중하게 사용하세요. 자세한 내용은 KILL(Transact-SQL)을 참조하세요.

AVAILABILITY_REPLICA log_reuse_wait

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

CHECKPOINT log_reuse_wait

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

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

USE dbname; CHECKPOINT
select * from sys.dm_db_log_info(db_id('dbname'))

log_reuse_wait 요소에 대한 자세한 정보

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

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

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

사용 가능한 디스크 공간

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

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

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

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

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

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

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

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

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

DECLARE @log_reached_disk_size BIT = 0

SELECT 
    name LogName, 
    physical_name, 
    CONVERT(bigint, size)*8/1024 LogFile_Size_MB, 
    volume_mount_point, 
    available_bytes/1024/1024 Available_Disk_space_MB,
    (CONVERT(bigint, size)*8.0/1024)/(available_bytes/1024/1024 )*100 file_size_as_percentage_of_disk_space,
    db_name(mf.database_id) DbName
FROM sys.master_files 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 are close to or completely filled disk volume they reside on.
    -- Either Add A New File To A New Drive, Or Shrink Existing File
    -- If Cannot Shrink, Go To Cannot Truncate Section

    DECLARE @db_name_filled_disk sysname, @log_name_filled_disk sysname, @go_beyond_size bigint 
    
    DECLARE log_filled_disk CURSOR FOR
        SELECT 
            db_name(mf.database_id),
            name
        FROM sys.master_files 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) DbName,
       name LogName,
       physical_name,
       type_desc ,
       CONVERT(bigint, SIZE)*8/1024 LogFile_Size_MB ,
       CONVERT(bigint,max_size)*8/1024 LogFile_MaxSize_MB ,
       (SIZE*8.0/1024)/(max_size*8.0/1024)*100 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 sysname, @log_name_max_size sysname, @configured_max_log_boundary bigint, @auto_grow 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입니다.

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

• 수동으로 파일 크기를 늘려 단일 증분을 생성합니다. 이는 로그 크기 증가 및 크기에 대한 일반적인 권장 사항입니다.
• ALTER DATABASE 문을 사용하여 FILEGROWTH 옵션에 대해 0이 아닌 증분을 설정하여 자동 증가를 설정합니다. SQL Server의 자동 증가 및 자동 축소 설정에 대한 고려 사항을 참조하세요.

다음 단계

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