[mssql] sql server에서 ldf의 파일의 용량이 왜 이렇게 크지?

개요

 

• 고객사에서 전체 인원 장애가 발생하여 확인 결과 하드디스크 용량이 부족한 것으로 확인
• LDF 파일의 용량이 1TB까지 증가하여 하드디스크를 과도하게 사용
• tomcat 로그에는 "데이터베이스 'xxx'의 트랜잭션 로그가 'LOG_BACKUP'(으)로 인해 꽉 찼습니다." 에러 출력
• "DBCC SHRINKFILE" 명령어 실행 시 용량이 1TB에서 11MB로 줄었는데, 데이터가 유실된 것은 아닌지 문의

 

 

MDF, LDF란?

 

MDF(Main Data File)와 LDF(Log Database File) 파일은 마이크로소프트 SQL 서버 데이터베이스의 기본 구성 요소이다.

• MDF : 데이터베이스의 스키마, 데이터, 테이블, 뷰, 프로시저, 트리거 등 핵심 데이터가 담기는 파일
• LDF : 모든 트랜잭션과 각 트랜잭션이 수행한 데이터베이스 수정 사항을 기록하는 로그 파일. 이 파일은 데이터 복구 시 필요.

 

 

트랜잭션 로그(LDF)에서 지원하는 작업

 

트랜잭션 로그는 다음 작업을 지원

• 개별 트랜잭션 복구.
• SQL Server 시작 시 모든 불완전한 트랜잭션을 복구합니다.
• 복원된 데이터베이스, 파일, 파일 그룹 또는 페이지를 오류 지점으로 롤포워드
• 트랜잭션 복제 지원
• 고가용성 및 재해 복구 솔루션 지원: AlwaysOn 가용성 그룹, 데이터베이스 미러링 및 로그 전달

 

 

LDF 용량 자동 증가 설정

 

아래의 순서로 이동하여 설정된 값 확인(SSMS 사용)

• 스키마에서 마우스 오른쪽 클릭
• "속성" 클릭
• "파일" 클릭

LDF 용량 자동 증가 설정

 

 

 

"자동 증가/최대 크기"의 "..."을 클릭하여 자동 증가 크기, 최대 크기 설정

LDF 용량 자동 증가 설정

 

 

 

 

 

 

DBCC SHRINKFILE

 

현재 데이터베이스의 지정된 데이터 또는 로그 파일 크기를 축소

DBCC SHRINKFILE 명령어는 DBCC에서 제공하는 명령어이고 구문 문법은 아래와 같이 정의

DBCC SHRINKFILE   
(  
    { file_name | file_id }   
    { [ , EMPTYFILE ]   
    | [ [ , target_size ] [ , { NOTRUNCATE | TRUNCATEONLY } ] ]  
    }  
)  
[ WITH NO_INFOMSGS ]

 

• file_name : 축소할 파일의 논리적 이름.
• file_id : 축소할 파일의 ID
• target_size : 축소하고자 하는 파일의 사이즈 (MB).  이 값을 지정하지 않거나 0으로 지정하면 DBCC SHIRINKFILE은 파일 초기 사이즈로 축소시킨다.
• EMPTYFILE : 다른 파일로 데이터를 옮긴다. 단, 같은 파일그룹 내 파일이여야 한다. (해당 포스트에서는 다루지 않는다.)
• NOTRUNCATE : target_percent, size의 지정 여부와 관계없이 데이터 파일의 끝에서 할당된 데이터를 파일 앞의 할당되지 않은 공간으로 이동만 한다. ( 파일이 축소되지 않은 것처럼 보인다. )
• TRUNCATEONLY : 파일 끝의 모든 사용 가능한 공간을 운영체제로 해제 (반납)한다. 단, 데이터의 이동은 없다.

 

SHRINKFILE 명령어는 아래 3가지 방법으로 사용할 수 있다.

1.  데이터 파일의 맨 뒤의 빈 공간을 잘라낸다.
2.  데이터 파일의 맨 뒤에서 target_size를 제외한 데이터를 파일 앞의 빈 공간으로 이동한다. (조각 모음) 
3.  2번, 1번을 차례대로 수행한다.

1번의 방법을 TRUNCATEONLY라고 하며 무조건 잘라내는 것을 의미한다.

2번의 방법을 NOTRUNCATE라고 하며 데이터를 이동만 할 뿐 파일의 크기는 잘라내지 않는다.

 

 

 

 

DBCC SHRINKFILE 명령어 실행 시, 용량이 감소하지 않는 문제

 

고객사에서 DBCC SHRINKFILE 실행 시, 1TB에서 11MB로 용량이 감소했는데, 사내에서 용량이 크게 감소하지 않는 현상 발생

확인 결과, Recovery model이 기존에 "full" 이었고 "simple"로 변경 후 "DBCC SHRINKFILE" 명령어를 실행한 것으로 확인

 

 

 

복구 모델(Recovery model)

 

MSSQL에서는 DB 백업에 대한 복구 모델로 3가지(Full, Bulk-logged, Simple)를 지원한다.
여기서 말하는 '복구 모델'이란, 해당 DB의 옵션이 트랜잭션 로그파일에 어떤식으로 기록하는지에 대한 정의를 내리는 부분이다.

 

SSMS에서는 다음과 같이 해당 DB 스키마가 어떤 복구 모델로 설정되어 있는지 확인 및 수정 가능

1. DB 스키마에서 마우스 오른쪽 클릭
2. "속성" 클릭
3. "옵션" 클릭
4. "복구 모델" 확인 및 변경

복구 모델(Recovery model)

 

 

 

복구 모델 종류

1. Full (전체 복구 모델)

• 모든 작업에 대한 트랜잭션 로그 기록
• 문제가 발생한 시점까지 원하는 시점을 선택하여 복구 가능
• Default로 설정되어 있는 복구 모델
• 모든 작업에 대해 로그가 기록되므로, 로그 데이터가 가장 많이 쌓이고 성능이 가장 떨어짐
• Full Backup, Differential Backup, Transaction Backup 모두 가능

 

2. Bulk-logged (대량 로그 복구 모델)

• 대량 로그 작업이 발생할 경우(BCP, BULK INSERT, INSERT INTO SELECT, CREATE INDEX, ALTER INDEX REBUILD 등) 에만 로그 파일에 이러한 작업이 일어났다는 것을 기록
• 대량 로그 작업이 일어난 이후에 추가되거나 변경된 내용은 트랜잭션 로그 파일에 기록하지 않음
• 백업의 끝으로는 복구가 가능
• 원하는 시점(백업의 중간 부분)으로는 복구 불가
• 대량 로그 작업 외의 내용은 로그에 기록하지 않음으로써, 로그가 더 적게 남게 되고 성능이 Full 모델에 비해 더 높음
• Full Backup, Differential Backup, Transaction Backup 모두 가능

 

3. Simple (단순 복구 모델)

• 사실상 트랜잭션 로그 파일에 아무것도 기록되지 않음
• SQL Server(DBMS)가 로그파일을 전적으로 관리하는 모델이며,  DBMS가 메모리와 HDD의 Sync를 맞추는 시점마다 Sync 작업 후 트랜잭션 로그를 삭제
• 트랜잭션 로그를 사용한 DB 복원 불가
• 장애 발생 시, Full Backup or Differential Backup만 가능
• 문제점이 발생된 시점까지는 데이터를 복구할 수 없고, 마지막에 백업 해놓은 데이터까지만 복구 가능

 

 

분석

 

SSMS에서 disk usage를 확인한 결과 아래 이미지와 같이 데이터 표시

"Log File Auto Growth"에서 이전 "Log File Auto Growth"의 10% 만큼 증가되어 표시되는 것 확인 가능
ex) 2.38 * 1.1 = 2.56

"Log File Auto Shrinks"  + "Log File Auto Growth"의 값이 다음 "Log File Auto Shrinks"에 표시되는 것 확인 가능

이런 현상이 disk 용량이 꽉 찰때까지 지속됨

disk의 용량이 꽉 찬 상태에서 복구 모델을 simple로 변경하여 디스크에 할당된 용량은 그대로인 상태에서 트랜젝션 데이터가 삭제가 된 것으로 추측

이후 "DBCC SHRINKFILE" 명령어를 실행하여 비어있는 디스크용량을 반환하여 할당된 용량이 감소된 것으로 추

 

sql server에서 ldf의 파일의 용량이 왜 이렇게 크지?

 

 

 

 

 

 

 

참고자료

 

 

트랜잭션 로그 - SQL Server

 

DBCC SHRINKFILE(Transact-SQL) - SQL Server

 

[MSSQL] 복구 모델(Recovery model) - Full / Bulk-logged / Simple

 

[MSSQL] DBCC SHRINKFILE - DB 데이터/로그 파일 축소하기 - 1편