SQL Server를 실행하는 컴퓨터에서 트랜잭션 로그가 예기치 않게 커지거나 가득 찬다

SQL Server를 실행하는 컴퓨터에서 트랜잭션 로그가
예기치 않게 커지거나 가득 찬다

이 문서가 적용되는 제품 보기.

기술 자료 ID	:	317375
마지막 검토	:	2007년 12월 11일 화요일
수정	:	5.2

이 페이지에서

요약

원인

커밋되지 않은 트랜잭션

매우 큰 트랜잭션

작업: DBCC DBREINDEX 및 CREATE INDEX

트랜잭션 로그 백업으로부터 복원하는 동안

클라이언트 응용 프로그램이 모든 결과를 처리하지 못하는 경우

트랜잭션 로그가 확장을 완료하기 전에 쿼리가 시간 초과되고 로그가 가득 찼다는 내용의 잘못된 오류 메시지가 나타나는 경우

복제되지 않은 트랜잭션

추가 정보

요약

SQL Server 7.0, SQL Server 2000 및 SQL Server 2005에서 자동 증가 설정을 사용하면 트랜잭션 로그 파일이 자동으로 확장됩니다.

일반적으로 검사점이나 트랜잭션 로그 백업이 트리거되는 트랜잭션 로그 잘라내기 간에 발생할 수 있는 최대 트랜잭션 수를 저장할 수 있을 때 트랜잭션 로그 파일의 크기는 안정화됩니다.

그러나 상황에 따라 트랜잭션 로그가 매우 커지고 공간이 부족하거나 가득 찰 수 있습니다. 일반적으로 트랜잭션 로그 파일이 사용 가능한 디스크 공간을 모두 차지하고 더 이상 확장할 수 없을 때 다음과 같은 내용의 오류 메시지가 나타납니다.

오류: 9002, 심각도: 17, 상태: 2
'%.*ls' 데이터베이스의 로그 파일이 꽉 찼습니다.

SQL Server 2005를 사용하는 경우 다음과 같은 내용의 오류 메시지가 나타납니다.

오류: 9002, 심각도: 17, 상태: 2
데이터베이스 '%.*ls'의 트랜잭션 로그가 꽉 찼습니다. 로그의 공간을 다시 사용할 수 없는 이유를 확인하려면 sys.databases의 log_reuse_wait_desc 열을 참조하십시오.

이 오류 메시지 이외에 SQL Server는 트랜잭션 로그 확장을 위한 공간 부족으로 인해 데이터베이스를 주의 대상으로 표시할 수 있습니다. 이 상황을 해결하는 방법에 대한 자세한 내용은 SQL Server 온라인 설명서의 "디스크 공간 부족" 항목을 참조하십시오.

또한 트랜잭션 로그 확장으로 인해 다음 상황이 발생할 수도 있습니다.

•	트랜잭션 로그 파일이 매우 커집니다.
•	트랜잭션이 실패하고 롤백이 시작될 수 있습니다.
•	트랜잭션을 완료하는 데 시간이 오래 걸릴 수 있습니다.
•	성능 문제가 발생할 수 있습니다.
•	차단이 발생할 수 있습니다.

위로 가기

원인

다음 이유나 시나리오로 인해 트랜잭션 로그 확장이 발생할 수 있습니다.

•	커밋되지 않은 트랜잭션
•	매우 큰 트랜잭션
•	작업: DBCC DBREINDEX 및 CREATE INDEX
•	트랜잭션 로그 백업으로부터 복원하는 동안
•	클라이언트 응용 프로그램이 모든 결과를 처리하지 못하는 경우
•	트랜잭션 로그가 확장을 완료하기 전에 쿼리가 시간 초과되고 로그가 가득 찼다는 내용의 잘못된 오류 메시지가 나타나는 경우
•	복제되지 않은 트랜잭션

참고 SQL Server 2005에서는 sys.databases 카탈로그 뷰의 log_reuse_wait 및 log_reuse_wait_desc 열을 검토하여 다음 사항을 확인할 수 있습니다.

•	트랜잭션 로그 공간이 다시 사용되지 않는 이유
•	트랜잭션 로그를 자를 수 없는 이유

커밋되지 않은 트랜잭션

명시적 트랜잭션은 명시적 COMMIT 또는 ROLLBACK 명령을 실행하지 않으면 커밋되지 않습니다. 이 상황은 주로 해당 ROLLBACK 명령 없이 응용 프로그램이 CANCEL 또는 Transact SQL KILL 명령을 실행할 때 발생합니다. 트랜잭션 취소가 발생하지만 롤백되지는 않습니다. 따라서 취소된 트랜잭션이 여전히 열려 있기 때문에 SQL Server는 이 이후에 발생하는 모든 트랜잭션을 자를 수 없습니다. DBCC OPENTRAN Transact-SQL 참조를 사용하여 특정 시간에 데이터베이스에 활성 트랜잭션이 있는지 확인할 수 있습니다. 이 특정 시나리오에 대한 자세한 내용은 Microsoft 기술 자료의 다음 문서를 참조하십시오.

295108 (http://support.microsoft.com/kb/295108/) 불완전한 트랜잭션으로 인해 많은 잠금이 유지되고 차단이 발생할 수 있다

171224 (http://support.microsoft.com/kb/171224/) INF: Transact-SQL KILL 명령의 작동 방법 이해

또한 SQL Server 온라인 설명서의 "DBCC OPENTRAN" 항목을 참조하십시오.

커밋되지 않은 트랜잭션이 발생할 수 있는 시나리오:

•	응용 프로그램 설계에서 모든 오류가 롤백의 원인이라고 가정합니다.
•	응용 프로그램 설계에서 명명 트랜잭션이나 특별히 중첩된 명명 트랜잭션으로 롤백할 때의 SQL Server 동작을 완전히 고려하지 않습니다. 내부 명명 트랜잭션으로 롤백하려고 하면 다음과 같은 내용의 오류 메시지가 나타납니다. 서버: Msg 6401, 수준 16, 상태 1, 줄 13 InnerTran을(를) 롤백할 수 없습니다. 해당 이름의 트랜잭션이나 저장점이 없습니다. SQL Server가 이 오류 메시지를 생성한 후에 다음 문으로 계속됩니다. 이것은 의도적으로 설계된 동작입니다. 자세한 내용은 SQL Server 온라인 설명서의 "중첩 트랜잭션" 또는 "Inside SQL Server" 항목을 참조하십시오. 다음과 같이 응용 프로그램을 설계하는 것이 좋습니다. • 한 트랜잭션 단위만 엽니다. 다른 프로세스가 사용자 프로세스를 호출할 가능성을 염두에 두십시오. • COMMIT, ROLLBACK, RETURN 또는 유사한 명령이나 문을 실행하기 전에 @@TRANCOUNT를 확인합니다. • 다른 @@TRANCOUNT가 사용자의 @@TRANCOUNT를 "중첩"할 수 있다는 가정 아래 코드를 작성하고 오류가 발생하면 외부 @@TRANCOUNT가 롤백되도록 계획합니다. • 트랜잭션을 위한 저장점과 표시 옵션을 검토합니다. 이것이 잠금을 해제하지는 않습니다. • 완벽한 테스트를 수행합니다.
•	응용 프로그램에서 트랜잭션 내에 사용자 개입을 허용합니다. 이렇게 하면 트랜잭션이 오래 열려 있어 자를 수 없게 되고 열려 있는 트랜잭션 이후의 로그에 새 트랜잭션이 추가되기 때문에 차단이 발생하고 트랜잭션 로그가 늘어납니다.
•	응용 프로그램에서 @@TRANCOUNT를 검사하여 열린 트랜잭션이 없는지 확인하지 않습니다.
•	네트워크 또는 기타 오류가 발생하면 이를 알리지 않고 SQL Server와 클라이언트 응용 프로그램의 연결을 끊습니다.
•	연결 풀링을 수행합니다. 작업자 스레드가 만들어지면 연결을 서비스하지 않아도 SQL Server에서 이 스레드를 다시 사용합니다. 사용자 연결이 트랜잭션을 시작하고 이 트랜잭션을 커밋하거나 롤백하기 전에 연결을 끊은 다음 연결이 같은 스레드를 다시 사용하면 이전 트랜잭션이 계속 열려 있게 됩니다. 이 경우 이전 트랜잭션을 계속 열어 두는 잠금이 발생하고 커밋된 트랜잭션을 로그에서 잘라내지 못하기 때문에 로그 파일 크기가 커지게 됩니다. 연결 풀링에 대한 자세한 내용은 Microsoft 기술 자료의 다음 문서를 참조하십시오. 164221 (http://support.microsoft.com/kb/164221/) INFO: ODBC 응용 프로그램에서 연결 풀링을 설정하는 방법

매우 큰 트랜잭션

트랜잭션 로그 파일의 로그 레코드는 트랜잭션 단위로 잘립니다. 트랜잭션 범위가 크면 이 트랜잭션이 완료된 경우에만 해당 트랜잭션과 그 이후에 시작된 트랜잭션이 트랜잭션 로그에서 제거됩니다. 이로 인해 로그 파일이 커질 수 있습니다. 트랜잭션이 너무 크면 로그 파일이 사용 가능한 디스크 공간을 모두 사용하게 되어 오류 9002 같은 "트랜잭션 로그 가득 참" 유형의 오류 메시지가 발생할 수 있습니다. 이러한 유형의 오류 메시지가 나타날 때 수행할 작업에 대한 자세한 내용은 본 문서의 "추가 정보" 절에 나와 있습니다. 또한 큰 트랜잭션을 롤백하는 데 시간이 오래 걸리고 SQL Server에서 오버헤드가 발생합니다.

작업: DBCC DBREINDEX 및 CREATE INDEX

SQL Server 2000의 복구 모델 변경으로 인해 전체 복구 모드를 사용하고 DBCC DBREINDEX를 실행하면 SELECT INTO 또는 BULK COPY를 사용하고 "Trunc. Log on chkpt."를 해제한 상태에서 동일한 복구 모드를 사용하는 SQL Server 7.0에 비해 트랜잭션 로그가 상당히 크게 확장될 수 있습니다.

DBREINDEX 연산 후 트랜잭션 로그의 크기가 문제가 될 수 있지만 이 방법은 더 나은 로그 복원 성능을 제공합니다.

트랜잭션 로그 백업으로부터 복원하는 동안

자세한 내용은 Microsoft 기술 자료의 다음 문서를 참조하십시오.

232196 (http://support.microsoft.com/kb/232196/) INF: 백업 복원 이후 로그 사용 공간이 증가한 것으로 나타난다

SQL Server 2000이 대량 기록 모드를 사용하도록 설정하고 BULK COPY 또는 SELECT INTO 문을 실행하면 변경된 모든 범위가 표시된 다음 트랜잭션 로그를 백업할 때 백업됩니다. 이렇게 하면 트랜잭션 로그를 백업하여 대량 작업을 수행한 후에도 오류로부터 복구할 수 있지만 트랜잭션 로그 크기가 추가됩니다. SQL Server 7.0에는 이 기능이 포함되어 있지 않습니다. SQL Server 7.0은 어느 범위가 변경되었는지 기록하지만 실제 범위는 기록하지 않습니다. 따라서 대량 기록 모드에서 로깅은 SQL Server 7.0보다 SQL Server 2000에서 더욱 많은 공간을 차지하지만 전체 모드의 경우만큼 크게 차지하지는 않습니다.

클라이언트 응용 프로그램이 모든 결과를 처리하지 못하는 경우

SQL Server에 쿼리를 실행하고 결과를 즉시 처리하지 않으면 잠금이 유지되어 서버의 동시성이 줄어들 수 있습니다.

예를 들어, 두 페이지의 행을 결과 집합에 채워야 하는 쿼리를 실행한다고 가정합니다. SQL Server는 쿼리를 구문 분석하고 컴파일하고 실행합니다. 이것은 쿼리를 만족해야 하는 행이 들어 있는 두 페이지에 공유 잠금이 있다는 것을 의미합니다. 또한 모든 행이 하나의 SQL Server TDS 패킷(서버가 클라이언트와 통신하는 방법)에 맞지 않다고 가정합니다. TDS 패킷은 채워져 클라이언트에 전송됩니다. 첫 번째 페이지의 모든 행이 TDS 패킷에 맞을 경우 SQL Server는 해당 페이지에 대한 공유 잠금을 해제하지만 두 번째 페이지에 대한 공유 잠금은 그대로 둡니다. 그런 다음 SQL Server는 클라이언트가 더 많은 데이터를 요청하기를 기다립니다. 예를 들어, 클라이언트는 DBNEXTROW/DBRESULTS, SQLNextRow/SQLResults 또는 FetchLast/FetchFirst를 사용하여 이를 수행할 수 있습니다.

즉, 클라이언트가 나머지 데이터를 요청할 때까지 공유 잠금이 유지됩니다. 따라서 두 번째 페이지의 데이터를 요청하는 다른 프로세스가 차단될 수 있습니다.

트랜잭션 로그가 확장을 완료하기 전에 쿼리가 시간 초과되고 로그가 가득 찼다는 내용의 잘못된 오류 메시지가 나타나는 경우

이 경우 충분한 디스크 공간이 있지만 공간이 부족하다는 내용의 오류 메시지가 계속 나타납니다.

이 상황은 SQL Server 7.0 및 SQL Server 2000에 따라 다릅니다.

트랜잭션 로그가 거의 꽉 차 있는 경우 쿼리로 인해 트랜잭션 로그가 자동 확장될 수 있습니다. 그러면 시간이 추가로 걸리고 이로 인해 쿼리가 중지되거나 시간 제한 기간을 초과할 수 있습니다. 이 경우 SQL Server 7.0은 오류 9002를 반환합니다. 이 문제는 SQL Server 2000에 적용되지 않습니다.

SQL Server 2000에서 데이터베이스에 대해 자동 축소 옵션을 설정한 경우에는 트랜잭션 로그가 자동으로 확장을 시도하기 위한 매우 짧은 시간이 있어도 자동 축소 기능이 동시에 실행되기 때문에 확장되지 않을 수 있습니다. 이 옵션은 또한 오류 9002이라는 잘못된 인스턴스를 유발할 수도 있습니다.

일반적으로 트랜잭션 로그 파일의 자동 확장은 빠르게 발생합니다. 그러나 다음 상황에서는 평상시보다 오래 걸릴 수 있습니다.

•	확장량이 너무 작은 경우
•	여러 가지 이유로 서버가 느린 경우
•	디스크 드라이브가 충분히 빠르지 않은 경우

복제되지 않은 트랜잭션

게시자 데이터베이스의 트랜잭션 로그 크기는 복제를 사용하는 경우 확장할 수 있습니다. 복제되는 개체에 영향을 주는 트랜잭션은 "For Replication"으로 표시됩니다. 커밋되지 않은 트랜잭션과 같은 이러한 트랜잭션은 검사점 후 또는 트랜잭션 로그를 백업한 후 로그 판독기 작업이 트랜잭션을 배포 데이터베이스에 복사하고 표시를 지울 때까지 삭제되지 않습니다. 로그 판독기 작업이 게시자 데이터베이스에서 이러한 트랜잭션을 읽지 못하도록 하는 문제가 있을 경우 트랜잭션 로그의 크기는 복제되지 않은 트랜잭션 수가 증가할 때 계속 확장될 수 있습니다. DBCC OPENTRAN Transact-SQL 참조를 사용하여 가장 오래된 복제되지 않은 트랜잭션을 식별할 수 있습니다.

복제되지 않은 트랜잭션 문제 해결에 대한 자세한 내용은 SQL Server 온라인 설명서의 "sp_replcounters" 및 "sp_repldone" 항목을 참조하십시오.

자세한 내용은 Microsoft 기술 자료의 다음 문서를 참조하십시오.

306769 (http://support.microsoft.com/kb/306769/) FIX: 스냅샷 게시된 DB의 트랜잭션 로그를 자를 수 없다

240039 (http://support.microsoft.com/kb/240039/) FIX: DBCC OPENTRAN이 복제 정보를 보고하지 않는다

198514 (http://support.microsoft.com/kb/198514/) FIX: 새 서버로 복원하면 트랜잭션이 로그에 남아 있다

위로 가기

추가 정보

데이터베이스의 트랜잭션 로그는 SQL Server가 로그 파일의 총 크기와 로그를 확장할 때 사용되는 확장량을 기반으로 내부적으로 그 크기를 결정하는 VLF(가상 로그 파일) 집합으로 관리됩니다. 로그는 항상 전체 VLF 단위로 확장되며 VLF 경계로만 압축할 수 있습니다. VLF는 ACTIVE, RECOVERABLE 및 REUSABLE이라는 세 가지 상태 중 하나로 존재할 수 있습니다.

•	ACTIVE: 로그의 활성 부분은 활성(커밋되지 않은) 트랜잭션을 나타내는 최소 LSN(로그 순서 번호)에서 시작합니다. 로그의 활성 부분은 마지막으로 작성된 LSN에서 끝납니다. 활성 로그가 일부라도 포함된 VLF는 활성 VLF로 간주됩니다. 실제 로그에서 사용되지 않는 공간은 VLF의 일부가 아닙니다.
•	RECOVERABLE: 가장 오래된 활성 트랜잭션 앞에 있는 로그 부분은 복구용으로 로그 백업의 순서를 유지하는 데만 필요합니다.
•	REUSABLE: 트랜잭션 로그 백업을 유지하지 않는 경우 또는 로그를 이미 백업한 경우 SQL Server는 가장 오래된 활성 트랜잭션 이전의 VLF를 다시 사용합니다.

SQL Server가 실제 로그 파일 끝에 도달하면 파일의 시작 부분에서 CIRCLING BACK 연산을 실행하여 실제 파일의 공간을 다시 사용하기 시작합니다. 사실상 SQL Server는 로그 파일에서 복구나 백업용으로 더 이상 필요하지 않은 공간을 재활용합니다. 로그 백업 순서가 유지되는 경우 이러한 로그 레코드를 백업하거나 자를 때까지 최소 LSN 이전의 로그 부분은 덮어쓸 수 없습니다. 로그 백업을 수행한 후에는 SQL Server가 파일의 시작 부분으로 다시 순환할 수 있습니다. SQL Server가 로그 파일에서 이전의 로그 레코드 작성을 시작하기 위해 순환한 후에 로그의 재사용 가능한 부분은 논리적 로그의 끝과 로그의 활성 부분 사이입니다.

자세한 내용은 SQL Server 온라인 설명서의 "트랜잭션 로그의 물리 아키텍처" 항목을 참조하십시오. 또한 "Inside SQL Server 7.0"(Soukup, Ron. Inside Microsoft SQL Server 7.0, Microsoft Press, 1999) 190페이지와 "Inside SQL Server 2000"(Delaney, Kalen. Inside Microsoft SQL Server 2000, Microsoft Press, 2000) 182-186페이지에 있는 자세한 다이어그램과 설명을 참조할 수 있습니다. SQL Server 7.0과 SQL Server 2000 데이터베이스에는 자동 증가와 자동 축소 옵션이 있습니다. 이러한 옵션을 사용하여 트랜잭션 로그를 축소하거나 확장할 수 있습니다.

이러한 옵션이 서버에 주는 영향에 대한 자세한 내용은 Microsoft 기술 자료의 다음 문서를 참조하십시오.

315512 (http://support.microsoft.com/kb/315512/) INF: SQL Server에서 자동 증가와 자동 축소 구성을 위한 고려 사항

트랜잭션 로그 파일 자르기와 압축 사이에는 차이점이 있습니다. SQL Server가 트랜잭션 로그 파일을 자르면 해당 파일의 해당 내용(예: 커밋된 트랜잭션)이 삭제됩니다. 그러나 Windows 탐색기나 dir 명령을 사용하여 디스크 공간 관점에서 파일 크기를 보면 크기는 그대로이지만 .ldf 파일 내의 공간을 새 트랜잭션에서 다시 사용할 수 있게 됩니다. SQL Server가 트랜잭션 로그 파일 크기를 축소할 때만 로그 파일의 실제 크기가 변경되는 것을 실제로 볼 수 있습니다.

트랜잭션 로그를 축소하는 방법에 대한 자세한 내용은 Microsoft 기술 자료의 다음 문서를 참조하십시오.

256650 (http://support.microsoft.com/kb/256650/) INF: SQL Server 7.0 트랜잭션 로그를 줄이는 방법

272318 (http://support.microsoft.com/kb/272318/) INF: SQL Server 2000에서 DBCC SHRINKFILE을 사용하여 트랜잭션 로그를 축소하는 방법

SQL Server 6.5 트랜잭션 로그 사용에 대한 자세한 내용은 Microsoft 기술 자료의 다음 문서를 참조하십시오.

110139 (http://support.microsoft.com/kb/110139/) INF: SQL 트랜잭션 로그가 가득 차는 원인


출처 : http://support.microsoft.com/kb/317375/ko