SQL Server 트랜잭션 로그 아키텍처(3/4) – 검사점 및 로그의 활성 부분

SQL Server 트랜잭션 로그 아키텍처(3/4)

– 검사점 및 로그의 활성 부분

 

• Version : SQL Server 2005, 2008, 2008R2, 2012

 

검사점은 현재 데이터베이스의 버퍼 캐시에 있는 커멋되지 않은 데이터 페이지를 디스크로 플러시 한다. 따라서 데이터베이스의 전체 복구 중에 처리되어야 하는 로그의 활성 부분이 최소화 된다. 전체 복구가 일어나는 동안 다음의 동작이 수행 된다.

• 시스템이 중단되기 전 디스크로 플러시되지 않은 로그의 수정 레코드가 롤포워드 된다.
• 커밋 또는 롤백 로그 레코드가 없는 불완전한 트랜잭션과 관련된 모든 수정 내용이 롤백 된다.

 

[검사점 수행 프로세스]

검사점 레코드에는 데이터베이스를 수정한 모든 활성 트랜잭션 목록이 포함 된다.

• 검사점의 시작을 표시하는 레코드를 로그 파일에 기록
• 검사점 로그 레코드의 체인에 검사점에 대해 기록된 정보를 저장. 이때 데이터베이스 차원의 롤백을 위하 반드시 첫 번째 레코드의 LSN이 있어야 한다.
• 데이터베이스에 단순 복구 모델을 사용하는 경우 MinLSN 앞에 나오는 공간을 재사용 가능으로 표시
• 커밋되지 않은 모든 로그와 데이터 페이지를 디스크에 쓴다.
• 검사점의 끝을 표시하는 레코드를 로그 파일에 쓴다
• 이 체인의 시작을 LSN을 데이터베이스 부팅 페이지에 쓴다.

 

 

[MinLSN 최소값 유형]

• 검사점 시작의 LSN
• 가장 오래된 활성 트랜잭션 시작의 LSN
• 아직 배포 데이터베이스로 전달되지 않은 가장 오래된 복제 트랜잭션 시작의 LSN

 

 

[검사점을 발생 시키는 작업]

• 명시적인 CHECKPOINT
• 데이터베이스에서 최소 로그 작업이 수행 된 경우(예:대량 복구 모델에서 대량 복사 작업 수행)
• ALTER DATABASE를 사용하여 데이터베이스 파일을 추가 또는 제거한 경우
• SQL Server 서비스를 중지한 경우
• SQL Server가 자동 검사점을 수행 한 경우
• 데이터베이스를 백업한 경우
• AUTO_CLOSE가 ON이고 마지막 사용자 연결이 닫힌 경우
• 데이터베이스를 다시 시작해야하는데 옵션 변경을 수행 한 경우

 

 

[자동 검사점]

SQL Server 데이터베이스 엔진에서는 자동 검사점을 생성한다. 자동 검사점의 간격은 마지막 검사점 이후 경과된 시간과 사용된 로그 공간에 따라 결정 된다. 즉 데이터 수정이 없다면 시간은 가변적이다. 또한 많은 데이터가 수정될 경우 자동 검사점이 자주 발생 할 수 있다.

 

자동 검사점은 데이터베이스가 단순 복구 모델을 사용하고 있을 때 트랜잭션 로그의 사용되지 않는 부분을 자른다. 그러나 전체 또는 대량 복구 모델인 경우 자동 검사점에서는 이러한 로그를 자르지 않는다.

 

데이터베이스에서 recovery interval 서버 구성 옵션을 사용하여 서버 인스턴스의 모든 데이터베이스에 대한 자동 검사점 간격을 계산할 수 있다. 이 옵션은 시스템 재시작 중에 데이터베이스 엔진이 데이터베이스를 복구하는데 사용하는 최대 시간을 지정 한다.

• 전체 또는 대량 복구 모델을 사용하는 데이터베이스의 경우 로그 레코드의 수가 데이터베이스 엔진에서 recovery interval 옵션에 지정된 시간 동안 처리할 수 있다고 예상한 레코드의 수에 도달할 때마다 자동 검사점이 생성 된다.
• 단순 복구 모델을 사용하는 데이터베이스의 경우 로그 레코드의 수가 다음의 두 값 중에서 작은 값에 도달할 때마다 자동 검사점이 생성 된다.

 

 

[자동 검사점 생성]

• 로그의 70%가 찼을 때
• 로그 레코드의 수가 데이터베이스 엔진의 recovery interval 옵션에 지정된 시간 동안 처리할 수 있다고 예상한 수에 도달 했을 때

 

 

[활성 로그]

MinLSN에서 마지막으로 쓰여진 로그 레코드까지의 로그 파일 부분을 로그의 활성 부분을 또는 활성로그 라고 한다.

활성 로그는 데이터베이스 전체 복구를 수행하는데 필요하므로 어떠한 부분도 잘라 낼 수 없다. 모든 로그 레코드는 로그의 MinLSN 앞에서 잘라야 한다.

 

아래 그림을 보면 Tran1, Tran2 의 시작이 LSN 141과 142로 시작 되었다. LSN 144에서 검사점이발생하고 Tran1은 LSN146 에서 커밋 되었다. 그리고 다시 한번 LSN 147에서 검사점이 발생 하였지만 Tran2의 경우는 LSN 148 상태에서도 여전히 사용 중이다. 마지막 레코드 LSN 148은 마지막 검사점 이후의 가장 오래된 활성 트랜잭션 상태이다. 그러면 레코드에서 MinLSN 시작은 어디 일까? 활성 로그에서 가장 오래된 LSN이 MinLSN이 된다. 따라서 Tran2가 시작된 142LSN이다.

 

 

[장기 실행 트랜잭션]

활성 로그에는 커밋되지 않은 모든 트랜잭션이 포함되어있다. 트랜잭션을 시작 했으나 커밋 또는롤백하지 않은 경우는 MinLSN을 앞당기지 못한다. 이로 인해 몇 가지 문제가 발생 할 수 있다.

• 데이터 변경 작업 후 커밋되지 않은 상태에서 시스템이 종료되면 시스템이 다시 시작된 후의 복구 단계 수행시 recovery interval 옵션에 지정된 시간보다 훨씬 더 오래 걸릴 수 있다.
• 로그가 MinLSN을 통과하여 잘릴 수 없으므로 로그 사이즈가 너무 커질 수 있다. 이러한 문제는 단순 복구 모델을 사용하는 경우에도 발생 할 수 있다.

 

 

[복제 트랜잭션]

활성 로그에는 복제용으로 표시 되었지만 아직 배포 데이터베이스로 전달되지 않은 모든 트랜잭션이 포함되어야 한다. 이러한 트랜잭션이 정상적으로 복제되지 않으면 로그 잘라내기가 수행되지 않을 수 있다.

 

 

[SSMS에서 recovery interval 설정]

 

 

[스크립트를 이용한 recovery interval 설정]

USE master; GO EXEC sp_configure 'recovery interval', '3'; RECONFIGURE WITH OVERRIDE;

 

 

 

[SQL Server 2012 CHECKPOINT 제어]

관련 링크 : http://sqlmvp.kr/140171578379

 

 

[참고자료]

http://msdn.microsoft.com/ko-kr/library/ms189573(v=sql.105).aspx

http://msdn.microsoft.com/ko-kr/library/ms190770(v=sql.105).aspx

http://msdn.microsoft.com/ko-kr/library/ms189085(v=sql.105).aspx

 

2013-04-18 / 강성욱 / http://sqlmvp.kr