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

적용 대상:SQL Server

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

참고

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

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

이 항목에서는 트랜잭션 로그가 꽉 찼을 때 알맞은 대처 방법에 대해 설명하고 앞으로 이런 상황을 방지하기 위한 방법을 제시합니다.

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

참고

이 문서에서는 SQL Server를 중점적으로 다룹니다. Azure SQL Database 및 Azure SQL Managed Instance의 이 오류에 관한 자세한 내용은 Azure SQL Database 및 Azure SQL Managed Instance 관련 트랜잭션 로그 오류 문제 해결을 참조하세요. Azure SQL Database 및 Azure SQL Managed Instance는 안정적인 최신 버전의 Microsoft SQL Server 데이터베이스 엔진을 기반으로 하므로 문제 해결 옵션과 도구는 다를 수 있지만 많은 내용이 유사합니다.

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

트랜잭션 로그가 가득 찬 경우의 적절한 대응 방법은 로그가 가득 차게 된 조건에 따라 달라집니다. 일반적인 원인은 다음과 같습니다.

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

가득 찬 트랜잭션 로그를 해결하는 방법

다음 구체적인 단계는 트랜잭션 로그가 가득 차는 이유를 확인하고 이 문제를 해결하는 데 도움이 됩니다.

1. 로그 잘라내기

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

로그 잘림 설명

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

경고

파일 축소를 위해 이동되는 데이터는 파일 내의 모든 사용 가능한 위치로 분산될 수 있습니다. 이로 인해 인덱스 조각화가 발생하여 인덱스 범위를 검색하는 쿼리 성능이 저하될 수 있습니다. 조각화를 방지하려면 축소 후 파일에 대한 인덱스를 다시 작성하는 것이 좋습니다. 자세한 내용은 데이터베이스 축소를 참조하세요.

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

지정된 사례에서 로그 잘림을 방지하는 방법을 알아보려면 sys.databases 카탈로그 뷰의 log_reuse_waitlog_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 db. Use a DMV which supports all supported versions

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

중요

데이터베이스 복구 중에 9002 오류가 발생한 경우에는 문제를 해결한 다음 ALTER DATABASE database_name SET ONLINE을 사용하여 데이터베이스를 복구합니다.

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 bs	
LEFT OUTER JOIN msdb.dbo.backupmediafamily 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)을 참조하세요.

ACTIVE_TRANSACTION log_reuse_wait

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

장기 실행 트랜잭션 검색

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

이 동적 관리 뷰는 데이터베이스 수준에서 트랜잭션 정보를 반환합니다. 장기 실행 트랜잭션과 특히 관련된 열에는 첫 번째 로그 레코드 시간(database_transaction_begin_time), 현재 트랜잭션 상태(database_transaction_state), 트랜잭션 로그에서 시작 레코드의 LSN(로그 시퀀스 번호)(database_transaction_begin_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입니다.

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

참고

두 경우 모두 현재 크기 제한에 도달하면 MAXSIZE 값을 늘리십시오.

참고 항목

ALTER DATABASE(Transact-SQL)
트랜잭션 로그 파일의 크기 관리
트랜잭션 로그 백업(SQL Server)
sp_add_log_file_recover_suspect_db(Transact-SQL)
MSSQLSERVER_9002
로그 파일 구조가 데이터베이스 복구 시간에 영향을 줄 수 있는 방법 - Microsoft 기술 커뮤니티