SQL 복구 모델 FULL(전체) 트랜잭션에 따른 로그 변화

처음 데이터베이스를 생성하면 기본값으로 RECOVERY 모드가 'FULL(전체)' 로 설정됩니다. 그렇다면 트랜잭션이 발생할 때마다 로그가 계속 증가해야 하는 게 일반적으로 알고 있는 이론입니다. 하지만 복구 모델이 FULL 로 설정되어 있다 하더라도 반드시 Full Backup 이 선행된 이후 시점부터 연속적으로 로그 레코드가 기록된다는 점 기억을 해 둬야 할 것 같습니다. 


복원 모드가 FULL 로 설정되어 있다 하더라도 Full Backup 을 한 번도 수행하지 않았다면 SIMPLE(단순) 복원 모드와 같이 트랜잭션이 발생할 때마다 로그 사이즈는 증가할 수 있습니다만 CHECKPOINT(검사점)이 발생한 이후 로그는 비워지게 됩니다. 아래 테스트로 확인해 볼 수 있었습니다.

-- 데이터베이스 생성

CREATE DATABASE LAIGO_FULL
ON (
       NAME = LAIGO_FULL,
       FILENAME = 'D:\SQL\LAIGO_FULL.MDF'
)
LOG ON (
       NAME = LAIGO_FULL_LOG,
       FILENAME = 'D:\SQL\LAIGO_FULL_LOG.LDF',
       SIZE = 1MB,
       MAXSIZE = 500MB,
       FILEGROWTH = 1MB
)

-- 데이터를 채우기 위한 테이블 생성 

USE LAIGO_FULL

CREATE TABLE TBL_DATA
(
       A INT,
       B CHAR(10),
       C CHAR(100)
)      

-- 데이터 삽입으로 트랜잭션 발생

SET NOCOUNT ON
GO

DECLARE @count INT
SET @count = 1

BEGIN TRAN
 WHILE @count < 300000
 BEGIN
       INSERT INTO TBL_DATA VALUES(@count, convert(char(10), @count), REPLICATE('A',100))
       SET @count = @count + 1
       INSERT INTO TBL_DATA VALUES(@count, convert(char(10), @count), REPLICATE('B',100))
       SET @count = @count + 1
       INSERT INTO TBL_DATA VALUES(@count, convert(char(10), @count), REPLICATE('C',100))
       SET @count = @count + 1
 END
COMMIT TRAN

-- 로그 파일 사용량 확인

DBCC SQLPERF(LOGSPACE)

-- LOG 파일의 크기가 141MB로 늘어났고 54% 를 사용중이라는 것을 확인할 수 있음

LAIGO_FULL        141.9922      54.67435           0

 
-- 검사점 수행 후 로그 확인

CHECKPOINT
DBCC SQLPERF(LOGSPACE)

-- LOG 파일 크기는 변화없고 사용 중인 로그가 12% 로 줄어들었음. CHECKPOINT 이후 로그가 비워짐

LAIGO_FULL       141.9922      12.65268           0

-- 추가 데이터 삽입 진행

DECLARE @count INT
SET @count = 1

BEGIN TRAN
 WHILE @count < 300000
 BEGIN
       INSERT INTO TBL_DATA VALUES(@count, convert(char(10), @count), REPLICATE('A',100))
       SET @count = @count + 1
       INSERT INTO TBL_DATA VALUES(@count, convert(char(10), @count), REPLICATE('B',100))
       SET @count = @count + 1
       INSERT INTO TBL_DATA VALUES(@count, convert(char(10), @count), REPLICATE('C',100))
       SET @count = @count + 1
 END
COMMIT TRAN

 
-- 로그 확인

DBCC SQLPERF(LOGSPACE)

-- 트랜잭션 로그 증가

LAIGO_FULL           141.9922      54.66541           0

-- 검사점 수행 후 로그 확인

CHECKPOINT
DBCC SQLPERF(LOGSPACE)

-- 마찬가지 CHECKPOINT 발생 이후 트랜잭션 로그가 비워지는 것이 확인됨

LAIGO_FULL           141.9922      12.76754           0

-- 전체 백업 수행 후 로그 확인

BACKUP DATABASE LAIGO_FULL TO DISK = 'D:\SQL\LAIGO_FULL.BAK'
DBCC SQLPERF(LOGSPACE)

-- 로그는 전혀 변화 없음

LAIGO_FULL      141.9922      54.61073           0

 

-- 추가 데이터 삽입 진행

DECLARE @count INT
SET @count = 1

BEGIN TRAN
 WHILE @count < 300000
 BEGIN
       INSERT INTO TBL_DATA VALUES(@count, convert(char(10), @count), REPLICATE('A',100))
       SET @count = @count + 1
       INSERT INTO TBL_DATA VALUES(@count, convert(char(10), @count), REPLICATE('B',100))
       SET @count = @count + 1
       INSERT INTO TBL_DATA VALUES(@count, convert(char(10), @count), REPLICATE('C',100))
       SET @count = @count + 1
 END
COMMIT TRAN

-- 로그 파일 확인 

DBCC SQLPERF(LOGSPACE)

-- 로그 파일 사이즈가 209MB로 증가함

LAIGO_FULL       209.9922      69.38549           0

-- 검사점 수행 후 로그 변화 확인

CHECKPOINT
DBCC SQLPERF(LOGSPACE)

-- 트랜잭션 로그는 비워지지 않음

LAIGO_FULL        209.9922      69.38991           0

-- 트랜잭션이 발생할 때마다 로그는 증가되는 것을 확인할 수 있습니다.  한 번 더 INSERT 를 진행하였을 때 아래와 같이 또 증가하였음을 확인할 수 있습니다.

LAIGO_FULL        277.9922      76.93913           0

테스트 과정에서 DBCC LOG, DBCC LOGINFO 명령을 통해 로그 변화를 모니터링 해 보면 좀 더 도움이 되었습니다. (석이 형님 고맙습니다)

'나의 데이터베이스는 복원 모드가 FULL 로 되어 있는데 왜? 예상했던 것과 달리 로그가 증가하지 않을까?' 라는 질문에 대한 답을 찾기 위해 테스트를 진행해 봤습니다... @.@ SQL Server 2000, 2005, 2008 모두 동일한 결과입니다.


[참고자료]
Transaction Log Physical Architecture
http://msdn.microsoft.com/en-us/library/ms179355(SQL.90).aspx


작성자 : Lai Go / 작성일자 : 2009.10.22