SQL Server

SQL Server 로깅과 복구의 이해

Paul S. Randal

한눈에 보기:

• SQL Server 로깅 및 복구의 작동 방식
• 트랜잭션 로그의 작동 방식과 이를 관리하기 위해 알아야 할 사항
• 복구 모델, 그리고 복구 모델이 로깅에 미치는 영향

목차

로깅이란?
복구란?
트랜잭션 로그
복구 모델
결론

SQL Server에서 가장 오해되는 부분으로 로깅과 복구 메커니즘이 있습니다. "본의 아니게" DBA를 맡은 사람들은 트랜잭션 로그가 존재하며, 이를 올바르게 관리하지 않을 경우 문제가 발생한다는 사실에 혼란을 느끼는 경우가 많은 것 같습니다. 트랜잭션 로그가 무한정 커질 수 있는 이유는 무엇인가요? 시스템 충돌 후에 데이터베이스가 온라인으로 돌아오는 데 종종 그렇게 오랜 시간이 걸리는 이유는 무엇인가요? 로깅을 완전히 끌 수 없는 이유는 무엇인가요? 어째서 데이터베이스를 제대로 복원할 수 없을까요? 트랜잭션 로그는 대체 무엇이며 왜 있는 것인가요?

이러한 질문들은 SQL Server 포럼과 뉴스 그룹에 반복해서 올라오는 질문들입니다. 그래서 이 기사에서는 로깅과 복구 시스템에 대한 개요를 설명하고 이러한 기능이 SQL Server 저장소 엔진에서 왜 필수적인 부분인지를 알아보고자 합니다. 트랜잭션 로그의 아키텍처를 설명하고, 데이터베이스에 사용할 수 있는 세 가지 복구 모델에 따라 트랜잭션 로그의 동작과 로깅 프로세스 자체가 어떻게 바뀔 수 있는지 알아보겠습니다. 그리고 그 과정에서 트랜잭션 로그를 관리하는 최선의 방법을 소개하는 리소스 링크도 제공하겠습니다.

로깅이란?

로깅과 복구가 SQL Server만의 고유한 개념은 아닙니다. 모든 상용 RDBMS(관계형 데이터베이스 관리 시스템)는 트랜잭션의 다양한 ACID 속성을 지원하기 위해 이러한 기능이 있어야 합니다. ACID는 RDBMS와 같은 트랜잭션 처리 시스템의 기본적인 속성인 원자성(Atomicity), 일관성(Consistency), 격리(Isolation), 그리고 영속성(Durability)을 의미합니다. 이에 대한 자세한 내용은 MSDN Library의 ACID 속성 섹션을 참조하십시오.

RDBMS의 작업은 데이터베이스의 저장소 구조에 일어난 일에 대해 물리적 및 논리적 수준에서 로깅(또는 기록)됩니다. 저장소 구조에 대한 각각의 변경에는 자체 로그 레코드가 있습니다. 이 레코드는 변경되는 구조와 변경된 내용에 대해 설명합니다. 이는 필요한 경우 변경을 재생하거나 되돌릴 수 있도록 수행됩니다. 로그 레코드는 트랜잭션 로그라고 하는 특수한 파일에 저장됩니다. 이 파일에 대해서는 조금 뒤에 자세히 설명하겠지만 우선은 일종의 순차 액세스 파일이라고 생각하면 됩니다.

이러한 하나 이상의 변경으로 구성된 집합은 트랜잭션에 그룹화될 수 있으며, 실제로 항상 이렇게 그룹화됩니다. 트랜잭션은 사용자, 응용 프로그램 개발자, 그리고 DBA에게 의미가 있는 데이터베이스에 대한 변경 수행의 기본 단위를 제공합니다(원자성). 트랜잭션은 성공(커밋)하거나 실패/취소(롤백)됩니다. 전자의 경우 트랜잭션을 구성하는 작업은 데이터베이스에 반영되도록 보장됩니다. 후자의 경우 작업은 데이터베이스에 반영되지 않도록 보장됩니다.

SQL Server의 트랜잭션은 명시적이거나 암시적입니다. 명시적 트랜잭션은 사용자나 응용 프로그램이 BEGIN TRANSACTION T-SQL 문을 실행하여 해당 세션에서 관련된 변경 그룹의 시작을 알리는 트랜잭션입니다. 명시적 트랜잭션은 COMMIT TRANSACTION 문이 실행되어 변경 그룹의 성공적인 완료를 알리면 성공합니다. ROLLBACK TRANSACTION 문이 실행되면 BEGIN TRANSACTION 문이 실행된 이후 해당 세션에 의해 수행된 모든 변경 내용이 변경 이전으로 되돌아가고(롤백) 트랜잭션은 중단됩니다. 뒤에 설명하겠지만 트랜잭션 롤백은 데이터베이스에 디스크 공간이 부족하거나 서버 충돌이 발생하는 등의 외부 이벤트에 의해 강제로 수행될 수도 있습니다.

암시적 트랜잭션은 사용자나 응용 프로그램이 T-SQL 문을 수행하기 전에 명시적으로 BEGIN TRANSACTION 문을 수행하지 않은 트랜잭션입니다. 데이터베이스에 대한 모든 변경은 트랜잭션이어야 하므로 저장소 엔진은 내부적으로 자동으로 트랜잭션을 시작합니다. T-SQL 문이 완료되면 저장소 엔진은 사용자 문을 래핑하기 위해 시작한 트랜잭션을 자동으로 커밋합니다.

하나의 T-SQL 문으로 데이터베이스 저장소 구조에 많은 변화가 발생하지는 않을 것이므로 이것이 불필요하다고 생각할 수도 있지만 ALTER INDEX REBUILD 문과 같은 경우를 고려해 보십시오. 이 문은 명시적 트랜잭션에 포함할 수 없지만 데이터베이스에 많은 변경을 일으킬 수 있습니다. 따라서 문제가 발생하면(예: 문이 취소됨) 모든 변경을 올바르게 되돌릴 수 있는 메커니즘이 필요합니다.

한 예로 암시적 트랜잭션에서 하나의 테이블 행이 업데이트될 때 일어나는 일을 살펴보겠습니다. 정수 열 c1과 문자 열 c2가 있는 간단한 힙 테이블이 있다고 가정해 보십시오. 이 테이블에는 10,000개의 행이 있으며 사용자가 다음과 같은 업데이트 쿼리를 제출합니다.

UPDATE SimpleTable SET c1 = 10 WHERE c2 LIKE '%Paul%';

다음과 같은 작업이 수행됩니다.

• 일치하는 행을 검색할 수 있도록 디스크에서 메모리(버퍼 풀)로 SimpleTable의 데이터 페이지를 읽습니다. WHERE 절 조건자와 일치하는 5개의 행이 3개의 데이터 페이지에 있는 것으로 나타납니다.
• 저장소 엔진이 자동으로 암시적 트랜잭션을 시작합니다.
• 업데이트를 허용하기 위해 3개의 데이터 페이지와 5개의 데이터 행이 잠깁니다.
• 메모리의 3개의 데이터 페이지에서 5개의 데이터 레코드가 변경됩니다.
• 디스크의 트랜잭션 로그에 있는 로그 레코드에도 변경이 기록됩니다.
• 저장소 엔진이 자동으로 암시적 트랜잭션을 커밋합니다.

업데이트된 3개의 데이터 페이지가 디스크에 다시 기록되는 단계가 목록에 포함되지 않은 이유는 아직 불필요하기 때문입니다. 변경 내용을 설명하는 로그 레코드가 디스크의 트랜잭션 로그에 있는 동안에는 변경 내용이 보호됩니다. 이후에 다시 페이지를 읽거나 변경해야 하는 경우에는 메모리에 이미 있는(디스크에는 아직 없음) 최신 페이지 복사본을 사용하면 됩니다. 데이터 페이지는 다음 검사점 작업이 수행되거나 버퍼 풀에 사용하는 메모리를 다른 페이지 이미지에 사용해야 하는 경우에 디스크에 기록됩니다.

검사점의 두 가지 목적은 쓰기 I/O를 일괄 처리하여 성능을 향상시키고 크래시 복구에 필요한 시간을 줄이는 것입니다. 성능 측면을 보면 데이터 페이지가 업데이트될 때마다 디스크에 기록해야 할 경우 사용량이 많은 시스템에서는 잦은 쓰기 I/O로 인해 I/O 하위 시스템이 과부하 상태에 이를 것입니다. 변경될 때마다 페이지를 기록하는 것보다는 주기적으로 더티 페이지(디스크에서 읽은 다음 변경된 페이지)를 기록하는 것이 좋습니다. 검사점의 복구에 대해서는 잠시 후에 설명하겠습니다.

검사점에 대한 일반적인 오해 중 하나는 커밋된 트랜잭션의 변경 내용만으로 페이지를 기록한다는 것입니다. 이는 사실이 아닙니다. 검사점은 페이지를 변경한 트랜잭션이 커밋되었는지 여부에 관계없이 항상 모든 더티 페이지를 기록합니다.

미리 쓰기 로깅은 변경 내용 자체가 기록되기 전에 변경 내용을 설명하는 로그 레코드를 디스크에 기록하는 메커니즘입니다. 이는 ACID 속성 중 영속성을 제공합니다. 변경 내용을 설명하는 로그 레코드가 디스크에 있는 한은 크래시가 발생하더라도 로그 레코드(그리고 변경 내용 자체)를 복구하여 트랜잭션의 효과가 손실되지 않도록 할 수 있습니다.

복구란?

로깅은 SQL Server의 다양한 작업을 지원합니다. 로깅은 크래시가 발생하는 경우 크래시 이후에 커밋된 트랜잭션이 올바르게 데이터베이스에 반영되도록 보장하며 커밋되지 않은 트랜잭션이 올바르게 롤백되고 데이터베이스에 반영되지 않도록 보장합니다. 또한 진행 중인 트랜잭션을 취소하고 모든 작업을 롤백할 수 있도록 하며, 트랜잭션 로그의 백업 복사본을 만들어 데이터베이스를 복원하고 트랜잭션 로그 백업을 재생함으로써 트랜잭션 측면에서 일관성이 확보된 특정 시점으로 데이터베이스를 되돌릴 수 있도록 합니다. 이 밖에도 복제, 데이터베이스 미러링 및 변경 데이터 캡처와 같이 트랜잭션 로그를 읽어야 하는 기능을 지원합니다.

이러한 로깅 사용에는 대부분 복구라는 메커니즘이 관련됩니다. 복구는 로그 레코드에 설명된 변경 내용을 데이터베이스에서 재생하거나 되돌리는 프로세스입니다. 로그 레코드를 재생하는 것을 복구의 다시 실행 또는 롤포워드 단계라고 하며, 로그 레코드를 되돌리는 것을 복구의 실행 취소 또는 롤백 단계라고 합니다. 즉, 복구는 트랜잭션 및 트랜잭션을 구성하는 모든 로그 레코드를 다시 실행하거나 실행 취소합니다.

단순한 형태의 복구는 단일 트랜잭션의 취소로, 이 경우 트랜잭션 실행이 취소되고 데이터베이스는 아무런 영향도 받지 않습니다. 가장 복잡한 형태는 크래시 복구로, 어떤 이유에서든 SQL Server에서 크래시가 발생하여 데이터베이스를 트랜잭션 측면에서 일관성 있는 시점으로 되돌려야 하는 경우입니다. 이는 크래시 발생 시점에 커밋된 모든 트랜잭션을 롤포워드하여 그 효과가 데이터베이스에 존속되도록 해야 한다는 것을 의미합니다. 그리고 크래시 발생 시점에 커밋되지 않은, 진행 중인 트랜잭션은 모두 롤백하여 그 효과가 데이터베이스에 존속되지 않도록 해야 합니다.

SQL Server의 트랜잭션에는 크래시 후에 작업을 계속하는 기능이 없기 때문입니다. 따라서 부분적으로 완료된 트랜잭션의 효과가 롤백되지 않으면 데이터베이스가 일관성이 없는 상태가 됩니다. 어떤 트랜잭션이 진행 중이었는지에 따라 구조적인 손상까지 발생할 수도 있습니다.

그렇다면 복구에서 수행할 작업은 어떻게 결정될까요? 모든 복구 프로세스는 각 로그 레코드에 LSN(로그 시퀀스 번호)이 부여된다는 사실을 바탕으로 작동합니다. 로그 시퀀스 번호는 트랜잭션 로그 내에서 로그 레코드의 위치를 고유하게 정의하는 세 부분으로 이루어진 숫자이며 계속해서 증가합니다. 트랜잭션의 각 로그 레코드는 트랜잭션 로그 내에 순차적으로 저장되며 트랜잭션 ID와 트랜잭션에 대한 이전 로그 레코드의 LSN을 포함합니다. 즉, 트랜잭션의 일부로 기록되는 각 작업에는 바로 이전 작업에 대한 "링크"가 있습니다.

단일 트랜잭션이 롤백되는 간단한 경우 복구 메커니즘은 최근 작업부터 최초 작업까지 로깅된 작업 체인을 따라가면서 각 작업이 수행된 순서의 반대 순서로 작업의 효과를 신속하고 편리하게 실행 취소할 수 있습니다. 트랜잭션에 의해 영향을 받은 데이터베이스 페이지는 아직 버퍼 풀에 있거나 디스크에 있습니다. 두 경우 모두 사용 가능한 페이지의 이미지는 트랜잭션의 효과가 페이지에 반영된 이미지가 확실하며 실행 취소가 필요합니다.

크래시 복구 동안의 메커니즘은 더 복잡합니다. 트랜잭션이 커밋될 때 데이터베이스 페이지가 디스크에 기록되지 않는다는 것은 디스크의 데이터베이스 페이지 집합이 트랜잭션 로그에 설명된 변경 내용 집합(커밋되거나 커밋되지 않은 트랜잭션에 대한 변경)을 정확하게 반영한다는 보장이 없음을 의미합니다. 그런데 아직 언급하지 않은 퍼즐의 마지막 조각이 있습니다. 모든 데이터베이스 페이지의 페이지 헤더(페이지에 대한 메타데이터가 포함된 8192바이트 페이지의 96바이트 부분)에는 페이지에 영향을 준 마지막 로그 레코드의 LSN이 포함된 필드가 있습니다. 이를 통해 복구 시스템은 복구해야 하는 특정 로그 레코드에 대해 수행할 작업을 결정할 수 있습니다.

• 데이터베이스 페이지의 LSN이 로그 레코드의 LSN과 같거나 큰 커밋된 트랜잭션의 로그 레코드의 경우에는 아무런 작업도 할 필요가 없습니다. 로그 레코드의 효과는 이미 디스크의 페이지에 존속되고 있습니다.
• 데이터베이스 페이지의 LSN이 로그 레코드의 LSN보다 작은 커밋된 트랜잭션의 로그 레코드의 경우에는 트랜잭션의 효과가 존속되도록 로그 레코드를 다시 실행해야 합니다.
• 데이터베이스 페이지의 LSN이 로그 레코드의 LSN과 같거나 큰 커밋되지 않은 트랜잭션의 로그 레코드의 경우에는 트랜잭션의 효과가 존속되지 않도록 로그 레코드를 실행 취소해야 합니다.
• 데이터베이스 페이지의 LSN이 로그 레코드의 LSN보다 작은 커밋되지 않은 트랜잭션의 로그 레코드의 경우에는 아무런 작업도 할 필요가 없습니다. 로그 레코드의 효과가 디스크의 페이지에 존속되지 않았으므로 실행 취소할 필요가 없습니다.

크래시 복구는 트랜잭션 로그를 읽어 나가면서 모든 커밋된 트랜잭션의 모든 효과가 데이터베이스에 존속되도록 하고, 모든 커밋되지 않은 트랜잭션의 모든 효과가 데이터베이스에 존속되지 않도록 합니다. 이것이 각각 다시 실행과 실행 취소 단계입니다. 크래시 복구가 완료되면 데이터베이스는 트랜잭션 측면에서 일관성 있는 상태가 되며 사용 가능하게 됩니다.

앞서 필자는 검사점 작업의 용도 중 하나가 크래시 복구에 소요되는 시간을 줄이는 것이라고 언급했습니다. 모든 더티 페이지를 주기적으로 디스크로 플러시하면 커밋된 트랜잭션에 의해 변경되었지만 디스크에 이미지가 없는 페이지의 수를 줄일 수 있습니다. 결과적으로 크래시 복구 중에 다시 실행 복구를 적용해야 하는 페이지의 수가 줄어듭니다.

트랜잭션 로그

크래시 복구는 트랜잭션 로그가 손상되지 않은 경우에만 가능합니다. 사실 트랜잭션 로그는 데이터베이스에서 가장 중요한 부분입니다. 크래시가 발생한 경우 데이터베이스에 대한 모든 변경 내용이 설명되어 있음을 보장할 수 있는 유일한 장소이기 때문입니다.

크래시 후에 트랜잭션 로그가 손실 또는 손상된 경우 크래시 복구를 완료할 수 없기 때문에 데이터베이스가 주의 대상이 됩니다. 이 경우에는 백업을 사용하여 데이터베이스를 복원하거나 응급 모드 복구와 같은 차선책을 사용하여 데이터베이스를 복구해야 합니다. 이러한 작업을 위한 절차는 이번 기사의 범위를 벗어나며 향후 기사에서 다룰 예정입니다.

트랜잭션 로그는 각 데이터베이스가 올바르게 작동하기 위해 반드시 필요한 특수한 파일입니다. 트랜잭션 로그는 로깅을 통해 생성된 로그 레코드를 유지하며, 복구 중에 이러한 로그 레코드를 다시 읽는 데 사용되거나 앞서 언급한 기타 로깅 사용 사례에서 사용됩니다. 로그 레코드 자체도 공간을 차지하지만 트랜잭션은 트랜잭션을 취소 또는 롤백해야 하는 경우에 필요한 잠재적인 로그 레코드를 위한 공간도 트랜잭션 로그에 예약합니다. 이러한 이유로 예를 들어 데이터베이스에서 50MB의 데이터를 업데이트하는 트랜잭션에 100MB의 트랜잭션 로그 공간이 필요한 경우가 생깁니다.

새 데이터베이스가 만들어지면 트랜잭션 로그는 기본적으로 비어 있습니다. 트랜잭션이 수행되면 트랜잭션 로그에 로그 레코드가 순차적으로 기록됩니다. 따라서 여러 개의 트랜잭션 로그 파일을 만들더라도 성능 향상은 없습니다(성능이 향상된다는 것은 가장 흔한 오해 중 하나임). 트랜잭션 로그는 각 로그 파일을 차례로 사용합니다.

동시 트랜잭션의 로그 레코드가 트랜잭션 로그에서는 분산되어 있을 수 있습니다. 단일 트랜잭션의 로그 레코드는 LSN으로 연결되므로 한 트랜잭션의 모든 로그 레코드를 그룹화할 필요는 없습니다. LSN은 일종의 타임스탬프라고 생각해도 무방합니다.

그림 1에는 트랜잭션 로그의 물리적인 아키텍처가 나와 있습니다. 트랜잭션 로그는 내부적으로 VLF(가상 로그 파일)라고 하는 더 작은 조각으로 나뉩니다. 이는 트랜잭션 로그의 내부적인 관리를 돕기 위한 것입니다. VLF가 가득 차면 로깅은 자동으로 트랜잭션 로그의 다음 VLF를 사용합니다. 트랜잭션 로그가 결국 모든 공간을 소진할 것이라고 생각할 수 있지만 바로 이 부분이 트랜잭션 로그가 데이터 파일과 다른 부분입니다.

그림 1 트랜잭션 로그의 물리적인 아키텍처

시작 부분에 있는 로그 레코드가 잘리거나 삭제되어 있다면 트랜잭션 로그는 순환 파일입니다. 로깅은 트랜잭션 로그의 끝에 도달하면 시작 부분으로 다시 래핑되어 원래 있던 부분을 덮어쓰기 시작합니다.

그렇다면 로그 레코드가 점유한 공간을 다시 사용할 수 있도록 하기 위해 로그 레코드는 어떤 방법으로 잘릴까요? 다음 조건을 모두 충족한다면 트랜잭션 로그의 로그 레코드는 더 이상 필요 없습니다.

• 로그 레코드가 속한 트랜잭션이 커밋됨
• 로그 레코드가 변경한 데이터베이스 페이지가 검사점에 의해 모두 디스크에 기록됨.
• 백업(전체, 차등 또는 로그)에 로그 레코드가 필요 없음
• 로그를 읽는 기능(예: 데이터베이스 미러링 또는 복제)에 로그 레코드가 필요 없음

필요한 상태의 로그 레코드를 활성 로그 레코드라고 하며, 하나 이상의 활성 로그 레코드를 가진 VLF 역시 활성 VLF라고 합니다. 트랜잭션 로그는 이따금 가득 찬 VLF의 모든 로그 레코드가 활성인지 아닌지 확인합니다. 모든 로그 레코드가 비활성이면 해당 VLF는 잘리도록 표시됩니다(트랜잭션 로그가 래핑되면 VLF를 덮어쓸 수 있음을 의미함). VLF를 자를 때는 실제로 이를 덮어쓰거나 지우는 것이 아니라 단순히 잘린 것으로 표시만 하며 나중에 다시 사용할 수 있습니다.

이 프로세스를 로그 자르기라고 하는데, 트랜잭션 로그의 크기를 실제로 줄이는 것과 혼동하면 안 됩니다. 로그 자르기를 수행하더라도 트랜잭션 로그의 실제 크기는 달라지지 않고, 트랜잭션 로그의 어떤 부분이 활성이고 어떤 부분이 비활성인지만 달라집니다. 그림 2는 그림 1의 트랜잭션 로그에 자르기를 수행한 후의 결과를 보여 줍니다.

그림 2 로그 자르기를 수행한 후의 트랜잭션 로그

활성 VLF는 논리 로그를 구성하며, 이는 트랜잭션 로그에서 모든 활성 로그 레코드를 포함하는 부분입니다. 데이터베이스는 크래시 복구가 로그의 활성 부분 내에서 로그 레코드 읽기를 시작해야 하는 위치를 알고 있습니다. 이 위치는 로그에서 가장 오래된 활성 트랜잭션의 시작인 MinLSN입니다(MinLSN은 데이터베이스 부트 페이지에 저장됨).

크래시 복구는 로그 레코드 읽기를 어디에서 중지해야 하는지 모르므로 트랜잭션 로그의 지워진 부분(트랜잭션 로그가 아직 래핑되지 않은 경우)에 도달하거나 패리티 비트가 이전 로그 레코드의 시퀀스와 맞지 않는 로그 레코드에 도달할 때까지 읽기를 계속합니다.

VLF가 잘리고 새로운 VLF가 활성화되면 실제 트랜잭션 로그 파일 내에서 논리 로그가 이동하며 최종적으로 그림 3에 나오는 것처럼 다시 시작 부분으로 래핑됩니다.

그림 3 트랜잭션 로그의 순환 특성

로그 자르기 가능 여부에 대한 검사는 다음과 같은 두 가지 상황에서 수행됩니다.

• 단순 복구 모델에서, 또는 전체 백업이 수행된 적이 없는 경우 다른 복구 모델에서 검사점이 발생할 때. 이는 전체 데이터베이스 백업이 수행되기 전까지는 데이터베이스가 단순 모델에서 전환된 후 의사 단순 복구 모델로 남아 있음을 의미합니다.
• 로그 백업이 완료될 때

여러 가지 이유로 로그 레코드를 활성 상태로 유지해야 할 경우 로그 자르기가 불가능할 수 있습니다. 로그 자르기를 수행할 수 없으면 VLF를 잘라낼 수 없고 결국 트랜잭션 로그가 커지거나 다른 트랜잭션 로그 파일이 추가되어야 합니다. 트랜잭션 로그의 과도한 증가는 VLF 조각화라고 하는 현상을 통해 성능 문제를 야기할 수 있습니다. VLF 조각화를 제거하면 로그와 관련된 작업의 성능이 크게 개선되는 경우가 있습니다.

이에 대한 자세한 내용은 Kimberly Tripp의 블로그 게시물 "트랜잭션 로그 처리량 향상을 위한 8가지 단계"를 참조하십시오. 이 게시물에서 Kimberly는 트랜잭션 로그 용량 계획, 관리 및 성능 향상과 관련된 최선의 방법을 소개합니다. 무척 유익한 게시물입니다.

로그 자르기를 방해하는 두 가지 일반적인 문제는 다음과 같습니다.

• 장기 실행 활성 트랜잭션. 가장 오래된 활성 트랜잭션의 첫 번째 로그 레코드부터 시작하는 전체 트랜잭션 로그는 트랜잭션이 커밋되거나 중단될 때까지 자를 수 없습니다.
• 전체 복구 모델로 전환하고 전체 백업을 수행한 후 로그 백업을 수행하지 않음. 전체 트랜잭션 로그는 활성 상태로 유지되면서 로그 백업에 의해 백업될 때까지 기다립니다.

로그 자르기를 방해하는 전체 요소 및 지침은 SQL Server 온라인 설명서의 "로그 잘림을 지연시킬 수 있는 요소" 항목을 참조하십시오. 필자는 제어되지 않는 트랜잭션 로그 증가의 영향과 VLF 조각화를 제거하는 방법을 보여 주는 비디오 데모도 만들었습니다. technetmagazine.com/videos에서 이 비디오 스크린캐스트 및 SQL 항목에 대한 이전 스크린캐스트를 확인하십시오.

트랜잭션 로그의 용량이 모두 차서 더 이상 증가할 수 없게 되면 오류 9002가 보고됩니다. 이 경우에는 공간을 늘리거나(예: 로그 파일의 크기를 늘림), 다른 로그 파일을 추가하거나, 로그 자르기에 방해가 되는 요소를 제거해야 합니다.

어떠한 상황에서도 트랜잭션 로그를 삭제하거나, 문서화되지 않은 명령을 사용하여 트랜잭션 로그를 다시 빌드하려고 시도하거나, BACKUP LOG(SQL Server 2008에서 제거됨)의 NO_LOG 또는 TRUNCATE_ONLY 옵션을 사용하여 잘라서는 안 됩니다. 이러한 옵션을 사용할 경우 트랜잭션 측면에서 일관성이 없어지고 손상이 발생하거나 올바르게 데이터베이스를 복구할 수 있는 여지가 없어집니다.

전체 트랜잭션 로그의 문제 해결에 대한 자세한 내용은 온라인 설명서의 "꽉 찬 트랜잭션 로그 문제 해결(오류 9002)" 항목을 참조하십시오.

복구 모델

트랜잭션 로그의 작동은 부분적으로 데이터베이스가 사용하는 복구 모델에 의해 결정됩니다. 사용할 수 있는 복구 모델에는 세 가지가 있으며 이러한 모델은 트랜잭션 로그의 작동이나 작업이 로깅되는 방법, 또는 이 두 가지 모두에 영향을 줍니다.

전체 복구 모델에서는 모든 작업의 모든 부분이 로깅되며 이를 완전 로깅이라고 합니다. 전체 복구 모델에서 전체 데이터베이스 백업이 수행되면 로그 백업이 수행될 때까지 트랜잭션 로그는 자동으로 잘리지 않습니다. 로그 백업 사용을 원하지 않고 데이터베이스를 특정 시점으로 복구하는 기능도 필요 없다면 전체 복구 모델을 사용하지 마십시오. 그러나 데이터베이스 미러링을 사용하려는 경우에는 선택의 여지가 없습니다. 데이터베이스 미러링은 전체 복구 모델만 지원하기 때문입니다.

BULK_LOGGED 복구 모델의 트랜잭션 로그 자르기 체계는 전체 복구 모델과 동일하지만 일부 작업의 부분적인 로깅을 허용하며, 이를 최소 로깅이라고 합니다. 이러한 작업의 예에는 인덱스 재작성과 일부 대량 로드 작업이 있습니다. 전체 복구 모델에서는 전체 작업이 로깅됩니다.

그러나 BULK_LOGGED 복구 모델에서는 할당 변경만 로깅되므로 생성되는 로그 레코드가 크게 감소하고 결과적으로 트랜잭션 로그가 증가할 여지도 줄어듭니다. 최소 로깅 작업에 대한 자세한 내용은 온라인 설명서의 "최소 로깅 가능한 작업" 섹션을 참조하십시오.

마지막으로 단순 복구 모델의 로깅 동작은 실질적으로 BULK_LOGGED 복구와 동일하지만 트랜잭션 로그 자르기 체계는 상당히 다릅니다. 단순 복구 모델에서는 로그 백업이 불가능하며 이는 검사점이 발생할 때 로그를 자를 수 있음을 의미합니다(로그 레코드를 활성 상태로 유지하는 다른 항목이 없는 한). 이러한 각 복구 모델에는 가능한 백업(또는 필요한 백업)과 다양한 시점으로 복구하는 기능 측면에서 나름의 장단점이 있습니다. 이에 대한 자세한 내용은 올해 추후 기사에서 다루도록 하겠습니다.

결론

이 기사에서는 SQL Server 동작의 중요한 부분을 학술적으로 설명했습니다. 이 기사로 여러분이 갖고 있던 오해가 해소되었기를 희망합니다. 로깅과 복구를 처음 접하는 독자라면 이 기사에서 다음과 같은 요점을 기억해 두시기 바랍니다.

• 로그 파일을 여러 개 만들지 마십시오. 그렇게 해도 성능은 향상되지 않습니다.
• 데이터베이스에서 사용 중인 복구 모델과 이 복구 모델이 트랜잭션 로그에 미치는 영향을 알아 두십시오. 특히 검사점이 발생할 때 자동 자르기가 가능한지 여부를 확인하십시오.
• 트랜잭션 로그 증가 가능성, 이러한 현상의 가능한 원인, 그리고 이러한 문제를 해결하는 방법을 알아 두십시오.
• 전체 트랜잭션 로그의 문제를 해결할 때 도움을 받을 수 있는 방법을 알아 두십시오.

필자의 블로그에는 트랜잭션 로그와 이에 영향을 주는 요인에 대한 다양한 정보가 있습니다. 자세한 내용은 "백업을 수행하기 전에 데이터베이스 크기 줄이기"를 참조하십시오. 트랜잭션 로그에 대한 다양한 온라인 설명서 항목을 찾아보는 것도 좋습니다. 트랜잭션 로그 관리부터 시작해 보십시오.

언제나 그렇듯이 의견이나 질문은 Paul@SQLskills.com으로 보내 주십시오.

이 기사를 기술적인 측면에서 검토한 Kimberly L. Tripp에게 감사 인사를 전합니다.

Paul S. Randal은 SQLskills.com의 관리 이사 겸 SQL Server MVP입니다. 1999년부터 2007년까지 Microsoft의 SQL Server 저장소 엔진 팀에서 근무한 Paul은 DBCC CHECKDB/repair for SQL Server 2005를 저술했으며 SQL Server 2008 개발 과정에서 핵심 저장소 엔진 부분을 담당했습니다. Paul은 재해 복구, 고가용성 및 데이터베이스 유지 관리 분야의 전문가이며 전 세계 각종 컨퍼런스에서도 꾸준히 의견을 발표하고 있습니다. 블로그 주소는 SQLskills.com/blogs/paul입니다.