트랜잭션 로그 파일의 크기 관리

적용 대상:SQL Server

이 문서에서는 SQL Server 트랜잭션 로그 크기를 모니터링하고 트랜잭션 로그를 축소하고 트랜잭션 로그 파일에 추가하거나 이 파일을 확장하고 tempdb 트랜잭션 로그 증가율을 최적화하고 트랜잭션 로그 파일 증가를 제어하는 방법을 설명합니다.

이 문서는 SQL Server에 적용됩니다. 매우 유사하지만 Azure SQL Managed Instance에서 트랜잭션 로그 파일 크기를 관리하는 방법은 Azure SQL Managed Instance에서 데이터베이스의 파일 공간 관리를 참조하세요. Azure SQL 데이터베이스에 대한 자세한 내용은 Azure SQL Database에서 데이터베이스의 파일 공간 관리를 참조하세요.

데이터베이스의 저장소 공간 형식 이해

다음 스토리지 공간 수량을 이해하는 것은 데이터베이스의 파일 공간을 관리하는 데 중요합니다.

다음 다이어그램에서는 데이터베이스에 대한 여러 스토리지 공간 유형 간의 관계를 보여 줍니다.

파일 공간 정보에 대한 단일 데이터베이스 쿼리

다음 쿼리를 사용하여 할당된 데이터베이스 파일 공간 크기와 할당된 사용되지 않은 공간 크기를 반환합니다. 쿼리 결과의 단위는 MB입니다.

-- Connect to a user database
SELECT file_id, type_desc,
       CAST(FILEPROPERTY(name, 'SpaceUsed') AS decimal(19,4)) * 8 / 1024. AS space_used_mb,
       CAST(size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS decimal(19,4)) AS space_unused_mb,
       CAST(size AS decimal(19,4)) * 8 / 1024. AS space_allocated_mb,
       CAST(max_size AS decimal(19,4)) * 8 / 1024. AS max_size_mb
FROM sys.database_files;

로그 공간 사용 모니터링

sys.dm_db_log_space_usage를 사용하여 로그 공간 사용을 모니터링합니다. 이 DMV는 현재 사용된 로그 공간 크기에 대한 정보를 반환하고 트랜잭션 로그 잘림을 수행해야 하는 시기를 나타냅니다.

현재 로그 파일 크기, 최대 크기 및 파일의 자동 증가 옵션에 대한 정보를 보려면 sys.database_files에서 해당 로그 파일의 size, max_size 및 growth 열을 사용하면 됩니다.

로그 파일 축소

파일의 여유 공간을 운영 체제로 반환하여 물리적 로그 파일의 실제 크기를 줄이려면 로그 파일을 축소합니다. 축소는 트랜잭션 로그 파일에 사용되지 않는 공간이 포함된 경우에만 차이가 납니다.

열린 트랜잭션으로 인해 로그 파일이 가득 차면 트랜잭션 로그 자름을 차단하는 원인을 조사합니다.

데이터베이스 파일 축소가 성능에 부정적인 영향을 미칠 수 있음을 알고 있어야 합니다. 축소 후 인덱스 유지 관리를 참조하세요.

트랜잭션 로그를 줄이기 전에 로그 잘림을 지연시킬 수 있는 요소를 염두에 두세요. 로그 축소 후 스토리지 공간이 다시 필요하면 트랜잭션 로그가 다시 커지고 로그 확장 작업 중에 성능 오버헤드가 발생합니다. 자세한 내용은 권장 사항을 참조하세요.

데이터베이스가 온라인 상태이고 하나 이상의 가상 로그 파일(VLF)에 여유 공간이 있는 경우에만 로그 파일을 축소할 수 있습니다. 경우에 따라 다음에 로그가 잘릴 때까지 로그를 축소하지 못할 수도 있습니다.

장기 실행 트랜잭션과 같이 오랜 시간 동안 VLF를 활성 상태로 유지하는 요인으로 인해 로그 축소가 제한되거나 로그가 전혀 축소되지 못할 수 있습니다. 자세한 내용은 로그 잘림을 지연시킬 수 있는 요소를 참조하세요.

로그 파일을 축소하면 논리 로그 부분이 포함되지 않은 하나 이상의 VLF(비활성 VLF)가 제거됩니다. 트랜잭션 로그 파일을 축소하면 비활성 VLF가 로그 파일 끝에서 제거되어 로그가 대략적인 대상 크기로 줄어듭니다.

축소 작업에 대한 자세한 내용은 다음 링크를 검토하세요.

로그 파일 축소(데이터베이스 파일의 축소 없이)

• DBCC SHRINKFILE(Transact-SQL)

• 파일 축소

로그 파일 축소 이벤트 모니터링

• Log File Auto Shrink Event Class입니다.

로그 공간 모니터링

• sys.dm_db_log_space_usage(Transact-SQL)

• sys.database_files(Transact-SQL)(로그 파일의 size, max_size 및 growth 열 참조)

축소 후 인덱스 유지 관리

데이터 파일에 대한 축소 작업이 완료되면 인덱스가 조각화될 수 있습니다. 이로 인해 대규모 검사를 사용하는 쿼리와 같은 특정 워크로드에 대한 성능 최적화 효과가 줄어듭니다. 축소 작업 완료 후 성능 저하가 발생하면 인덱스 유지 관리를 통해 인덱스를 다시 빌드하는 것을 고려해 봅니다. 인덱스를 다시 빌드하려면 데이터베이스에 사용 가능한 공간이 필요하므로 할당된 공간이 증가하여 축소 효과가 무효화될 수 있습니다.

인덱스 유지 관리에 대한 자세한 내용은 쿼리 성능 향상 및 리소스 소비 감소를 위한 인덱스 유지 관리 최적화를 참조하세요.

로그 파일 추가 또는 확장

디스크 공간이 충분한 경우 기존의 로그 파일을 확장하거나 일반적으로 다른 디스크에 있는 데이터베이스에 로그 파일을 추가하여 공간을 확보할 수 있습니다. 로그 공간이 부족하고 로그 파일을 보관하는 볼륨에 디스크 공간이 부족한 경우가 아니라면 하나의 트랜잭션 로그 파일로 충분합니다.

• 로그 파일을 데이터베이스에 추가하려면 ALTER DATABASE 문의 ADD LOG FILE 절을 사용합니다. 로그 파일을 추가하면 로그가 확장될 수 있습니다.
• 로그 파일을 확대하려면 ALTER DATABASE 문의 MODIFY FILE 절을 사용하여 SIZE 및 MAXSIZE 구문을 지정합니다. 자세한 내용은 ALTER DATABASE(Transact-SQL) 파일 및 파일 그룹 옵션을 참조하세요.

자세한 내용은 권장 사항을 참조하세요.

tempdb 트랜잭션 로그 크기 최적화

서버 인스턴스를 다시 시작하면 tempdb 데이터베이스의 트랜잭션 로그 크기가 자동 증가 이전의 원래 크기로 조정됩니다. 이 경우 tempdb 트랜잭션 로그 성능이 저하될 수 있습니다.

서버 인스턴스를 시작하거나 다시 시작한 후에 tempdb 트랜잭션 로그 크기를 늘려 이 오버헤드를 방지할 수 있습니다. 자세한 내용은 tempdb Database을(를) 참조하세요.

트랜잭션 로그 파일 증가 제어

트랜잭션 로그 파일의 증가를 관리하기 위해 ALTER DATABASE(Transact-SQL) 파일 및 파일 그룹 옵션 문을 사용합니다. 다음 사항에 유의하세요.

• 현재 파일의 크기(KB, MB, GB 및 TB 단위)를 변경하려면 SIZE 옵션을 사용합니다.
• 증분을 변경하려면 FILEGROWTH 옵션을 사용합니다. 값 0은 자동 증가를 사용하지 않고 추가 공간을 허용하지 않음을 나타냅니다.
• 로그 파일 최대 크기(KB, MB, GB 및 TB 단위)를 제어하거나 증가를 UNLIMITED로 설정하려면 MAXSIZE 옵션을 사용합니다.

자세한 내용은 권장 사항을 참조하세요.

권장 사항

다음은 트랜잭션 로그 파일 작업 시 일반적으로 권장되는 사항입니다.

• FILEGROWTH 옵션으로 설정된 대로 트랜잭션 로그의 자동 증가(자동 증가) 증분은 워크로드 트랜잭션의 요구를 앞설 수 있도록 커야 합니다. 로그 파일의 파일 증가분이 충분히 커야 자주 확장하는 번거로움을 피할 수 있습니다. 트랜잭션 로그의 크기를 적절히 조정하는 좋은 방법은 다음과 같은 시간 동안 사용된 로그의 양을 모니터링하는 것입니다.

  • 완료될 때까지 로그를 백업할 수 없기 때문에 전체 백업을 실행하는 데 필요한 시간.
  • 가장 큰 인덱스 유지 보수 작업에 필요한 시간.
  • 데이터베이스에서 가장 큰 일괄 처리를 실행하는 데 필요한 시간.

• FILEGROWTH 옵션을 사용하여 데이터 및 로그 파일에 대해 자동 증가를 설정한 경우, 백분율은 계속 증가하는 양이기 때문에 백분율보다 크기에서 설정하는 것이 확장률을 더 잘 제어할 수 있습니다.

  • SQL Server 2022(16.x) 이전 버전에서 트랜잭션 로그는 인스턴트 파일 초기화를 사용할 수 없으므로 확장된 로그 증가 시간이 특히 중요합니다.

  • SQL Server 2022(16.x)(모든 버전)부터 Azure SQL Database에서 인스턴트 파일 초기화를 통해 최대 64MB의 트랜잭션 로그 증가 이벤트가 구현될 수 있습니다. 새 데이터베이스의 기본 자동 증가 크기 증분은 64MB입니다. 64MB보다 큰 트랜잭션 로그 파일 자동 증가 이벤트는 즉시 파일 초기화의 이점을 얻을 수 없습니다.

  • 모범 사례에서는 트랜잭션 로그에 대해 FILEGROWTH 옵션 값을 1,024MB 이상으로 설정하지 않습니다. FILEGROWTH 옵션에 대한 기본값은 다음과 같습니다.

    버전	기본값
    SQL Server 2016(13.x)로 시작	데이터는 64MB입니다. 로그 파일은 64MB입니다.
    SQL Server 2005(9.x)로 시작	데이터는 1MB입니다. 로그 파일은 10%입니다.
    SQL Server 2005(9.x) 이전	데이터는 10%입니다. 로그 파일은 10%입니다.

• 작은 자동 증가 증분에서 너무 많은 작은 VLF를 생성하여 성능이 저하될 수 있습니다. 지정된 인스턴스에 있는 모든 데이터베이스의 현재 트랜잭션 로그 크기에 대한 최적의 VLF 분포 및 필수 크기가 달성되도록 필수 증가 증분을 결정하려면 SQL Tiger Team에서 제공하는 VFL 분석 및 수정용 스크립트를 참조하세요.

• 큰 자동 증가 증분으로 인해 두 가지 문제가 발생할 수 있습니다.

  • 큰 자동 증가 증분으로 인해 새 공간이 할당되는 동안 데이터베이스가 일시 중지되어 쿼리 제한 시간이 초과될 수 있습니다.
  • 큰 자동 증가 증분에서 너무 적고 큰 VLF를 생성하여 성능도 영향을 받을 수 있습니다. 지정된 인스턴스에 있는 모든 데이터베이스의 현재 트랜잭션 로그 크기에 대한 최적의 VLF 분포 및 필수 크기가 달성되도록 필수 증가 증분을 결정하려면 SQL Tiger Team에서 제공하는 VFL 분석 및 수정용 스크립트를 참조하세요.

• 자동 증가를 사용하는 경우에도 쿼리의 요구 사항을 충족시킬 정도로 빠르게 커질 수 없는 경우 트랜잭션 로그가 꽉 찼다는 메시지를 받을 수 있습니다. 증가 증분 변경 방법에 대한 자세한 내용은 ALTER DATABASE(Transact-SQL) 파일 및 파일 그룹 옵션을 참조하세요.

• 트랜잭션 로그 파일이 동일한 파일 그룹의 데이터 파일처럼 비례 채우기를 사용하지 않기 때문에 데이터베이스에 여러 로그 파일을 저장해도 성능이 향상되지 않습니다.

• 로그 파일은 자동으로 축소되도록 설정할 수 있습니다. 그러나 이것은 권장되지 않으며auto_shrink 데이터베이스 속성은 기본적으로 FALSE로 설정됩니다. auto_shrink를 TRUE로 설정하면 파일 공간의 25% 이상이 사용되지 않을 때만 자동 축소에 의해 파일 크기가 줄어듭니다.

  • 파일은 파일의 25%만 사용되지 않을 때의 크기 또는 파일의 원래 크기 중 더 큰 크기로 축소됩니다.
  • auto_shrink 속성의 설정 변경에 대한 자세한 내용은 데이터베이스의 속성 보기 또는 변경 또는 ALTER DATABASE SET 옵션(Transact-SQL)을 참조하세요.

다음 단계

• BACKUP(Transact-SQL)
• 꽉 찬 트랜잭션 로그 문제 해결(SQL Server 오류 9002)
• SQL Server 트랜잭션 로그 아키텍처 및 관리 가이드의 트랜잭션 로그 백업
• 트랜잭션 로그 백업(SQL Server)
• ALTER DATABASE (Transact-SQL) 파일 및 파일 그룹 옵션