모든 DB의 로그 잘림 확인하기

데이터베이스의 사이즈를 모니터링 하는 것은 DBA의 중요한 업무중 하나 인데요.
트랜잭션 로그가 채워지지 않도록 트랜잭션 로그를 정기적으로 잘라야 합니다.
잘라야 합니다. 자르다...
어감이 마치... 10기가 짜리 로그 파일을 1기가로 줄이는... 그런거 같습니다.
근데 아니에요.
10기가를 1기가로 줄이는것은 로그 자르기가 아니고 파일 축소 작업입니다.
DBCC SHRINKFILE
명령을 이용해서 줄입니다.
이것도 맘대로 줄일 수 있는게 아니고 비활성 로그 부분만 줄일 수 있습니다.
로그를 자르고 나면 그 잘린 부분이 "비활성 로그"입니다.

그럼 로그는 언제 잘라질까요?
단순 복구 모델에서는 Checkpoint 발생이후 잘립니다.
전체 복구 모델이나 대량 로그 복구 모델에서는 일단 로그 백업이 한번 되고 난 후 Checkpoint가 발생하면 잘립니다.
로그가 잘렸다... 라는 것은 재사용 가능하게 바뀌었다라는 것을 뜻합니다.

트랜잭션 로그 파일은 내부적으로는 VLF 라는 걸로 나우어져서 사용됩니다.
예를 들어서 8기가 짜리 로그 파일을 만들었다면
대략 512메가 짜리 16개의 VLF 파일이 생깁니다.
그리고 처음부터 하나씩 사용되어 집니다.
로그가 잘릴일이 없으면 트랜잭션 로그는 계속 늘어나게 됩니다.
그러다 보면 데이터 파일은 1기가인데 로그 파일이 100기가가 되는 그런 기현상도 벌어지죠.

로그파일에 대한 정보는
DBCC LOGINFO WITH TABLERESULTS, NO_INFOMSGS
명령을 이용해서 볼 수 있습니다.
결과는 다음과 같습니다.


각각의 열은 하나의 VLF를 나타냅니다.
FileSize는 KB입니다.
Status가 2인 VLF가 현재 사용중이거나 아직 잘리지 않은 로그입니다.
Status가 0인 VLF는 비활성 로그입니다. 잘린거죠.
잘린 로그는 재사용되어집니다.
DBCC SHRINKFILE (LogFileName, TRUNCATEONLY)
명령을 사용하면 마지막 활성 로그 이후의 공간을 운영체제에 반환하게 됩니다.
10기가 짜리 로그 파일이 1기가가 되는게 이 상황입니다.
만약 잘린 로그가 없다면 로그 파일은 무한정 커지게 됩니다.
무한용량 하드가 있다면 걱정없겠지만.... 포멧은 언제하나...

앞서 로그백업 후 Checkpoint가 발생하면 로그가 잘린다고 했었는데요.
로그가 잘린 이후 활성로그가 하나만 남는것이 이상적이랍니다.
활성로그가 여러개인 경우는 여러가지 경우가 있을 수 있겠는데
엄청나게 긴 트랜잭션이 VLF 여러개에 로그를 기록중인데 아직 트랜잭션이 끝나지 않은 경우
또는 그리 길지 않은 트랜잭션인데 VLF크기가 너무 작아서 좀 길다... 싶으면 여러개 잡아 먹는 경우가 있을 수 있겠는데요.
긴 트랜잭션이면 짧게 끝나도록 수정하면 되겠지만
VLF가 작은 경우는 문제가 좀 있습니다.
남아 있는 비활성 로그가 없을 경우 트랜잭션 로그를 기록 하기 위해서 로그파일을 늘려야 할텐데요.
이때 OS에 파일 크기를 늘려달라는 요청을 하게 될겁니다.
로그 기록하기도 바쁜데 파일 크기까지 늘려야 하니 쿼리 수행은 당연히 느려질겁니다.

그래서 제일 이상적인 상태는
로그 백업을 하는 주기 동안 더 이상 늘어나지 않아도 될만큼 충분한 로그 파일을 확보하고
그 안에서 VLF의 숫자가 적당히 존재하는게 최적일 겁니다. 적당히... 적당한 연봉은??
그리고 로그 백업 후에는 활성 로그가 하나만 남아 있는것이 좋을것입니다.
VLF의 크기를 수동으로 조절하면 좋겠지만 아쉽게도 그런 옵션은 없습니다.
VLF의 크기는 처음 로그 파일을 만들때 결정됩니다.
대략 8기가 짜리 로그 파일을 만들면 512메가로 16개가 생깁니다.
그래서 처음부터 용량 계획을 하고 적당한 크기로 만들어 두는게 좋습니다.
만약 처음부터 적당한 용량으로 만들지 못했다면
일단 무슨 수를 쓰던 모든 로그를 비활성으로 만든 후  (백업을 하고 Checkpoint 수행)
파일을 줄이고 적당한 크기로 늘려놓으면 됩니다.
그래도 처음 두개의 vlf는 크기를 조정할 수 없습니다.

이상은 로그파일에 대한 설명이었고
다음 스크립트는 모든 DB에 대한 VLF의 갯수와 활성VLF 숫자를 조회하는 쿼리입니다.
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SET NOCOUNT ON

IF OBJECT_ID('tempdb..#LOGINFO', 'U') IS NOT NULL DROP TABLE #LOGINFO
IF OBJECT_ID('tempdb..#LOGINFOTemp', 'U') IS NOT NULL DROP TABLE #LOGINFOTemp

CREATE TABLE #LOGINFO (FileID INTEGER,
                       FileSize DECIMAL(28, 0),
                       StartOffset DECIMAL(28, 0),
                       FSeqNo DECIMAL(28, 0),
                       Status TINYINT,
                       Parity TINYINT,
                       CreateLSN VARCHAR(30),
                       DatabaseName VARCHAR(1000),
                       DatabaseID INTEGER)
CREATE TABLE #LOGINFOTemp (FileID INTEGER,
                           FileSize DECIMAL(28, 0),
                           StartOffset DECIMAL(28, 0),
                           FSeqNo DECIMAL(28, 0),
                           Status TINYINT,
                           Parity TINYINT,
                           CreateLSN VARCHAR(30),
                           DatabaseName VARCHAR(1000),
                           DatabaseID INTEGER)
DECLARE @SQL VARCHAR(MAX)
SET @SQL = ''
SELECT @SQL = @SQL + 'USE [' + NAME + ']' + CHAR(10) +
                     'INSERT INTO #LOGINFOTemp (FileID, FileSize, StartOffset, FSeqNo, Status, Parity, CreateLSN)' + CHAR(10) +
                     'EXEC( ''DBCC LOGINFO WITH TABLERESULTS, NO_INFOMSGS'')' + CHAR(10) +
                     'UPDATE #LOGINFOTemp SET DatabaseName = ''' + NAME + ''', DatabaseID = DB_ID()' + CHAR(10) +
                     'INSERT INTO #LOGINFO SELECT * FROM #LOGINFOTemp' + CHAR(10) +
                     'DELETE #LOGINFOTemp' + CHAR(10)
  FROM SYS.DATABASES WHERE DATABASE_ID != DB_ID('tempdb')
EXECUTE (@SQL)
SELECT DatabaseName, DatabaseID, FileID, FileCount = COUNT(1), ActiveCount = COUNT(CASE WHEN Status > 0 THEN 1 END)
  FROM #LOGINFO
 GROUP BY DatabaseName, DatabaseID, FileID
 ORDER BY CASE WHEN DatabaseID < 5 THEN 0 ELSE 1 END, DatabaseName

결과는 다음과 같습니다.