안녕하세요~^^

LDF는 순환구조 형태로 되어있습니다.

아래 나오는 네모를 VLF라고 생각하시면 됩니다.

□: 트랜잭션 로그 백업이 되었거나 혹은 아직 사용되지 않은 것(DBCC LOGINFO 에서 Status 가 0인 값)

■: 현재 사용중이거 혹은 이미 사용했으나 트랜잭션 로그 백업이 되지 않은 것(DBCC LOGINFO 에서 Status 가 2인 값)

기본

■□□□□□□□□□□□□

DML이 많이 발생하면... 이렇게 로그가 많이 사용이 되겠지요~!

■■■■■■■□□□□□□

여기서 트랜잭션 로그 백업을 수행하면.. 아래처럼 현재 사용중인 VLF는 제외하고.. 백업이 됩니다.

□□□□□□■□□□□□□

하지만... 대량의 IO가 유발되고... 트랜잭션 로그 백업 시점에도 트랜잭션이 진행중이라면.. 아래 처럼 될 수도 있습니다 ^^

이 내용은 하단에 링크 참고하세요~

□□■■■■■□□□□□□

다시 원래대로 돌아와서..

DML이 발생하면서..  끝까지 다 쓰게 되면.. 순환해서 앞에서 부터 다시 이용하게 됩니다.

□□□□□□■■■■■■■

이런 식입니다.

■■■□□□■■■■■■■

이러다... 만약 전부 다 쓰게 되면.. 아래 처럼되겠죠~!

■■■■■■■■■■■■■

이 상태에선..더이상 공간이 없기 때문에.. 추가 사이즈를 할당하게 됩니다.

이런 식이 되겠죠. 추가되는 공간은.. 비율 혹은 MB로 설정하도록 되어있습니다.

■■■■■■■■■■■■■□□□

이러다보면... LDF 사이즈가 계속 증가가 되고... VLF 갯수역시 늘어나게 됩니다.

LDF 사이즈를 늘어나지 않게 하려면..반드시 트랜잭션 로그 백업을 수행해야 합니다.

이렇게 증가할때마다 VLF 갯수가 늘어나게 되고... 몇천개씩 되는 문제가 발생하게 됩니다.

보통.. 데이터베이스 잘 모르시는 분들이 풀백업만 수행하면 된다고 생각하는데.. 이는 정말 잘못된 생각입니다.

그러다... LDF 사이즈가 너무 커져서.. 줄이는 방법을 검색하게 되죠.

LDF 사이즈를.. 초반에 8GB로 설정하게 되면.. 16개의 VLF가 생성되게 됩니다.(각각 500MB 정도 되겠네요)

트랜잭션 백업직후 로그자르기 실패 확인

http://www.sqler.com/129852

이 글을 보시면.. Status가 2개 이상인 경우 .. DBA에게 알람을 주도록 권고 하고 있습니다.

개당 VLF 사이즈가 넉넉하기 때문에(500MB) .. 백업하면.. 반드시 한개만 사용하고 있다고 표시가 되야합니다.

하지만... 오랜 시간동안 처리되는 쿼리같은 경우.. 그리고 대량의 IO가 발생한다면... 여러 VLF에 걸쳐서 처리가 될 수 있습니다.

이 녀석 같은 경우는... 아직 트랜잭션이 끝나지 않았기 때문에.. 

트랜잭션 로그 백업을 수행하더라도 백업이 되질 않고 모두 2로 남아버리게 됩니다.

마지막으로.. (물론 정확한건 테스트를 해봐야 알겠지만..) MSDN에서 말하는 백업복원이 느려지는 경우는..

아무래도 VLF가 몇천개이상씩 있게 되면...  백업을 하게되는 경우.. 여러 개의 VLF를 읽으면서 백업이 되야하기 때문에 그런걸로 생각이 되네요~~ 복원 역시 마찬가지일테고요 ^^;

현재 38GB도 엄청 큰 사이즈로 판단됩니다. 보통 5G ~ 8G 정도로 설정해서 사용하시길 권장드립니다.

대량의 DML이 발생된다면.. 사이즈가 커질 수 있습니다.

참고가 되셨으면 좋겠네요~

감사합니다. ^^

추가 정보 입니다.

가상로그파일에 따른 성능 : http://sqlmvp.kr/140164396941

트랜잭션 로그 물리 아키텍처 : http://sqlmvp.kr/140187358202

Lazy Log Truncation : http://sqlmvp.kr/140198487987

LDF 파일이 잘리지 않는 이유 : http://sqlmvp.kr/140194327713

좋은 답변 다들 감사합니다 

특히 ■□를 이용한 표현 정말로 좋네요~~!!

성욱님 민석님 추가 댓글 감사드려요 ~ 잘봤습니다~! ^^*