트랜잭션 로그 파일의 크기 관리
적용 대상: 
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 해당 로그 파일에 대한 열 및 growth 열을 사용할 sizemax_size수도 있습니다.
Important
로그 디스크가 오버로드되지 않도록 하세요. 로그 스토리지가 트랜잭션 로드에 대한 IOPS 및 지연 시간 요구 사항을 견딜 수 있는지 확인하세요.
로그 파일 축소
사용 가능한 공간을 운영 체제로 반환하여 실제 크기를 줄이도록 로그 파일을 축소합니다. 축소는 트랜잭션 로그 파일에 사용되지 않는 공간이 포함된 경우에만 차이가 납니다.
열린 트랜잭션으로 인해 로그 파일이 가득 차면 트랜잭션 로그 자름을 차단하는 원인을 조사합니다.
주의
축소 작업을 정기적인 유지 관리 작업으로 간주해서는 안 됩니다. 반복되는 일상 업무에 따라 증가하는 데이터와 로그 파일은 축소 작업이 필요하지 않습니다. 축소 명령은 실행하는 동안 데이터베이스 성능에 영향을 줍니다. 사용량이 적은 기간 동안 실행해야 합니다. 일반 애플리케이션 워크로드로 인해 파일이 동일한 할당된 크기로 다시 증가하는 경우 데이터 파일을 축소하지 않는 것이 좋습니다.
데이터베이스 파일 축소의 잠재적인 부정적인 성능 영향에 유의하세요. 축소 후 인덱스 유지 관리를 참조하세요.
트랜잭션 로그를 줄이기 전에 로그 잘림을 지연시킬 수 있는 요소를 염두에 두세요. 로그 축소 후 스토리지 공간이 다시 필요한 경우 트랜잭션 로그가 다시 증가하여 로그 증가 작업 중에 성능 오버헤드가 발생합니다. 자세한 내용은 권장 사항을 참조하세요.
데이터베이스가 온라인 상태이고 하나 이상의 가상 로그 파일(VLF)에 여유 공간이 있는 경우에만 로그 파일을 축소할 수 있습니다. 경우에 따라 다음 로그 잘림 후에만 로그를 축소할 수 있습니다.
장기 실행 트랜잭션과 같이 오랜 시간 동안 VLF를 활성 상태로 유지하는 요인으로 인해 로그 축소가 제한되거나 로그가 전혀 축소되지 못할 수 있습니다. 자세한 내용은 로그 잘림을 지연시킬 수 있는 요소를 참조하세요.
로그 파일을 축소하면 논리 로그 부분이 포함되지 않은 하나 이상의 VLF(비활성 VLF)가 제거됩니다. 트랜잭션 로그 파일을 축소하면 비활성 VLF가 로그 파일 끝에서 제거되어 로그가 대략적인 대상 크기로 줄어듭니다.
축소 작업에 대한 자세한 내용은 다음 링크를 검토하세요.
로그 파일 축소(데이터베이스 파일의 축소 없이)
로그 파일 축소 이벤트 모니터링
로그 공간 모니터링
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) 파일 및 파일 그룹 옵션 문을 사용합니다. 다음 사항에 유의하세요.
'SIZE' 옵션을 사용하여 현재 파일 크기를 KB, MB, GB 및 TB 단위로 변경합니다.
- 증분을 변경하려면
FILEGROWTH옵션을 사용합니다. 값이 0이면 자동 증가가 꺼지고 추가 공간이 허용되지 않음을 나타냅니다. 'MAXSIZE' 옵션을 사용하여 로그 파일의 최대 크기를 KB, MB, GB 및 TB 단위로 제어하거나 증가를 UNLIMITED로 설정합니다.
자세한 내용은 권장 사항을 참조하세요.
권장 사항
다음은 트랜잭션 로그 파일로 작업할 때 권장되는 몇 가지 일반적인 권장 사항입니다.
옵션에 의해 설정된
FILEGROWTH대로 트랜잭션 로그의 자동 증가(자동 증가) 증가는 워크로드 트랜잭션의 요구 사항을 미리 유지할 수 있을 만큼 커야 합니다. 로그 파일의 파일 증가분이 충분히 커야 자주 확장하는 번거로움을 피할 수 있습니다. 트랜잭션 로그의 크기를 적절히 조정하는 좋은 방법은 다음과 같은 시간 동안 사용된 로그의 양을 모니터링하는 것입니다.- 로그 백업이 완료될 때까지 발생할 수 없으므로 전체 백업을 실행하는 데 필요한 시간입니다.
- 가장 큰 인덱스 유지 보수 작업에 필요한 시간.
- 데이터베이스에서 가장 큰 일괄 처리를 실행하는 데 필요한 시간.
옵션을 사용하여
FILEGROWTH데이터 및 로그 파일에 대해 자동 증가를 설정하는 경우 백분율이 계속 증가하는 양이므로 증가 비율을 더 잘 제어할 수 있도록 백분율 대신 크기로 설정하는 것이 좋습니다.SQL Server 2022(16.x) 이전 버전에서는 트랜잭션 로그가 빠른 파일 초기화를 사용할 수 없으므로 확장된 로그 증가 시간이 특히 중요합니다.
SQL Server 2022(16.x)(모든 버전)부터 Azure SQL Database에서 인스턴트 파일 초기화를 통해 최대 64MB의 트랜잭션 로그 증가 이벤트가 구현될 수 있습니다. 새 데이터베이스의 기본 자동 증가 크기 증분은 64MB입니다. 64MB보다 큰 트랜잭션 로그 파일 자동 증가 이벤트는 즉시 파일 초기화의 이점을 얻을 수 없습니다.
트랜잭션 로그에 대해 옵션 값을 1,024MB 이상으로 설정하는
FILEGROWTH것이 가장 좋습니다. 옵션의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 팀에서 제공하는 VLF를 분석하고 수정하기 위한 이 스크립트를 참조하세요.
큰 자동 증가 증분으로 인해 두 가지 문제가 발생할 수 있습니다.
- 큰 자동 증가 증분으로 인해 새 공간이 할당되는 동안 데이터베이스가 일시 중지되어 쿼리 제한 시간이 초과될 수 있습니다.
- 큰 자동 증가 증분에서 너무 적고 큰 VLF를 생성하여 성능도 영향을 받을 수 있습니다. 지정된 인스턴스에 있는 모든 데이터베이스의 현재 트랜잭션 로그 크기에 대한 최적의 VLF 배포와 필요한 크기를 달성하는 데 필요한 증가 증분을 확인하려면 SQL Tiger 팀에서 제공하는 VLF를 분석하고 수정하기 위한 이 스크립트를 참조하세요.
- 큰 자동 증가 증분으로 인해 새 공간이 할당되는 동안 데이터베이스가 일시 중지되어 쿼리 제한 시간이 초과될 수 있습니다.
자동 증가가 활성화되어 있더라도 쿼리의 요구 사항을 충족할 만큼 빠르게 확장할 수 없는 경우 트랜잭션 로그가 가득 찼다는 메시지를 받을 수 있습니다. 증가 증분 변경 방법에 대한 자세한 내용은 ALTER DATABASE(Transact-SQL) 파일 및 파일 그룹 옵션을 참조하세요.
트랜잭션 로그 파일은 동일한 파일 그룹의 데이터 파일과 같은 비례 채우기를 사용하지 않으므로 데이터베이스에 여러 로그 파일이 있으면 성능이 향상되지 않습니다.
로그 파일은 자동으로 축소되도록 설정할 수 있습니다. 그러나 권장되지 않으며 auto_shrink 데이터베이스 속성은 기본적으로 FALSE로 설정됩니다. auto_shrink를 TRUE로 설정하면 파일 공간의 25% 이상이 사용되지 않을 때만 자동 축소에 의해 파일 크기가 줄어듭니다. - 파일의 25%만 사용되지 않는 공간인 크기 또는 더 큰 파일의 원래 크기로 축소됩니다. - auto_shrink 속성의 설정을 변경하는 방법에 대한 자세한 내용은 데이터베이스의 속성 보기 또는 변경 및 ALTER DATABASE SET 옵션(Transact-SQL)을 참조하세요.