[SQL Server / MS-SQL] VLF (가상로그파일 - Virtual Log File)

 

 

---

 

VLF(가상로그파일 - Virtual Log File) 이란?

SQL Server 에서 페이지(Page) 는 데이터 파일 내에서 데이터를 저장하는 가장 작은 저장 단위이다.
이처럼 VLF(Virtual Log File) 는  물리적인 트랜잭션 로그 파일(LDF) 내에서 로그 데이터를 관리하는 논리적 단위이다.
SQL Server는 로그 파일의 크기에 따라 VLF의 수와 크기를 결정하는데, VLF 는 로그 파일 크기가 확장이 되면 VLF 갯수 역시 증가하게 된다.

데이터베이스 복구 프로세스의 초기 단계에서 SQL Server 는 모든 트랜잭션 로그 파일의 모든 VLF 를 검색하고, 목록을 작성한다.
VLF 가 많으면 이 단계에서 VLF 를 검색하고, 목록을 작성하는 시간이 그만큼 오래걸리게 된다.
VLF 수가 많아지면 아래와 같은 현상들이 발생할 수 있다.
 1) 데이터베이스 기동 시간 증가
 2) 데이터베이스 복원 시간 증가
 3) 데이터베이스 연결 시도 시간 증가
 4) 트랜잭션 로그 백업 및 복원 시간 증가
 5) 트랜잭션 복제본 대기 시간 증가

고로, VLF의 수를 적절히 관리하여야 한다.
VLF 파일의 갯수는 아래 명령어를 통해 확인할 수 있다.

DBCC LOGINFO ([DB명]);

Status	내용
0	- 트랜잭션 로그 백업이 된 VLF  - 아직 사용되지 않은 VLF
2	- 현재 사용중인 VLF  - 모두 사용했으나 트랜잭션 로그 백업이 되지 않은 VLF

 

그리고 아래 쿼리를 통해 DB별 VLF 갯수와 용량을 확인해볼 수 있다.

SELECT name AS 'DBName',
	   COUNT(l.database_id) AS 'VLF_TotalCount',
	   SUM(CAST(vlf_active AS INT)) AS 'VLF_UseCount',
	   COUNT(l.database_id)-SUM(CAST(vlf_active AS INT)) AS 'VLF_InuseCount',
	   SUM(vlf_size_mb) AS 'VLF_TotalSize(MB)',
	   SUM(vlf_active*vlf_size_mb) AS 'VLF_UseSize(MB)',
	   SUM(vlf_size_mb)-SUM(vlf_active*vlf_size_mb) AS 'VLF_InuseSize(MB)'
FROM sys.databases s
CROSS APPLY sys.dm_db_log_info(s.database_id) l
GROUP BY name
ORDER BY COUNT(l.database_id);

 

VLF 갯수에 따른 성능 비교

A 라는 로그 기본 사이즈를 1MB, AUTOGROWTH(자동 증가 증분) 를 1MB
B 라는 로그 기본 사이즈를 100MB, AUTOGROWTH(파일 자동 증분) 를 1MB

위와 같이 설정한 후,
각각 트랜잭션 로그 파일을 1MB 씩 자동 증가 시켜 200MB 를 만들어주자.

그렇다면 A 로그는 총 20번을 증가했을 것이고, B 로그는 총 2번이 증가되었을 것이다.
그렇게 되었을 때 A 로그의 VLF 가 212 개, B 로그의 VLF 가 6 개를 가지게 된다.

이 둘의 DML 과 백업시간을 비교해보면 소량이지만, B 로그보다 A 로그의 수행 속도가 더 걸린다.
동일한 용량이지만, VLF 크기와 갯수로 인해 작업 속도를 영향받을 수 있게 된다.

작은 자동 증가 증분으로 인해 너무 작고 많은 VLF 가 생성이 되면 해당  로그 파일을 읽고 쓰는 데 더 많은 시간과 리소스가 필요하게 된다. 반대로, 큰 자동 증가 증분으로 인해 새 공간이 할당되는 동안 데이터베이스가 일시 중지가 될 수 있어 적절한 크기의 로그와 자동 증가 증분을 설정하는 것이 중요하다.
또, VLF 갯수가 계속 늘어나지 않도록 주기적인 트랜잭션 로그 백업을 하여 VLF 를 관리해주는 것이 중요하다.

 

 

VLF 비우기

트랜잭션 로그 파일(.ldf) 가 거의 꽉찬 DB 가 있다.

LOGINFO 명령어를 통해 VLF 을 조회해보았다.
총 879 개의 VLF 이 조회되었고, 상태가 모두 사용중(Status: 2) 였다.

DBCC LOGINFO ('DB');

트랜잭션 로그 백업으로 VLF 을 비워보자.

BACKUP LOG [DB]
TO DISK = 'F:\TRANSACTION_LOG\DB_TRANSACTION_LOG.trn';

 

트랜잭션 로그 백업 완료 후, 다시 용량과 VLF 갯수 및 상태를 확인해보았다.
트랜잭션 로그 파일의 사이즈가 대폭 줄었음을 확인할 수 있다.

VLF 갯수에는 변함이 없이 879 개이고,
Status 값이 사용중인 것 빼곤 모두 0(사용 가능) 으로 변경된 것을 확인할 수 있다.

*879개의 VLF 를 비우는데 약 24분정도가 소요되었다. 실제로 이렇게 쌓이지 않도록 주기적인 트랜잭션 로그 백업을 해야한다.

또, 트랜잭션 로그 백업을 하는 것은 사용중인 VLF 파일을 사용 가능상태로 바꿔주는 것이지 VLF 갯수를 줄여주는 것은 아니다.

그렇다면 사용하지 않은 VLF 갯수는 어떻게 줄여줄 수 있냐?
아래 SHRINKFILE 명령을 통해 가능하다.

DBCC SHRINKFILE('LOG명',원하는파일사이즈);

나는 위 DB_log 를 10000MB 로 축소하였고, VLF 갯수를 조회해보니 185개로 줄었음을 확인할 수 있었다.

DBCC SHRINKFILE('DB_log',10000);

VLF 의 크기와 갯수는 초기의 로그 파일 크기와 AUTOGROWTH 설정에 큰 영향을 준다.
따라서, 너무 많은 작은 크기의 VLF 가 생성되지 않고 너무 적은 큰 크기의 VLF 가 생성되지 않도록 초기 구성을 잘 해주어야한다.
최적의 VLF 배포를 하기위해서는 초기의 로그 파일 크기를 최종 크기에 가깝게 설정하고, 상대적으로 큰 AUTOGROWTH 값을 설정해야한다.

 

VLF 에서 어떤 VLF 를 먼저 사용할까?

초기 생성 시, VLF 는 첫 번째 VLF 부터 순차적으로 사용된다.
모든 VLF 가 사용된 후에는 다시 첫 번째 VLF 로 돌아가서 사용한다.
이는 트랜잭션 로그의 순환적 방식이며, 이는 로그 파일이 계속해서 성장하는 것을 방지하고, 로그 파일 공간을 효율적으로 사용하기 위함이다.
백업이나 체크포인트로 인해 VLF 가 비워져 재사용 가능한 상태가 되면 가장 오래된 사용 가능한 VLF 부터 사용하게 된다.
만약, 모든 VLF 가 사용중일 경우에는 로그 파일이 증가하며(자동 증가의 경우) VLF 가 새로 생성된다. 

 

*참고문헌

https://learn.microsoft.com/ko-kr/sql/relational-databases/logs/manage-the-size-of-the-transaction-log-file?view=sql-server-ver16#Recommendations

트랜잭션 로그 파일 크기 관리 - SQL Server