출처 : http://support.microsoft.com
요약
SQL Server 트랜잭션 로그가 가득 차면 데이터베이스에서 CHECKPOINT를 비롯하여 UPDATE, DELETE 또는 INSERT 작업을 더 이상 할 수 없습니다. 이러한 상황은 아래와 같은 오류 1105로 나타납니다.
Can't allocate space for object syslogs in database dbname because the logsegment is full. If you ran out of space in syslogs, dump the transaction log. Otherwise use ALTER DATABASE or sp_extendsegment to increase the size of the segment.
master 또는 tempdb를 비롯한 어떤 데이터베이스에서나 이러한 상황이 발생할 수 있습니다. 본 문서에서는 오류 1105를 일으키는 문제점에 대한 가능한 원인과 해결 방법을 설명합니다. 트랜잭션 로그가 가득 차서 오류 1105가 발생한 경우에는 DUMP TRANSACTION 문을 사용하여 로그를 비워야 합니다. DUMP TRANSACTION의 사용 방법에 대한 자세한 내용은 SQL Server 설명서를 참조하십시오.
추가 정보
Microsoft SQL Server 같은 전형적인 관계형 데이터베이스의 기본 특성은 트랜잭션 무결성을 보장하는 것입니다. 모든 트랜잭션은 시스템 오류가 발생한 경우에도 변경 내용이 모두 적용되거나 전혀 적용되지 않는다는 점에서 완전한 원자성(즉, 개별적으로 기능을 수행)을 가져야 합니다. 사용자 정의 트랜잭션에서 BEGIN TRANSACTION 문과 COMMIT TRANSACTION 문 사이에 있는 모든 문은 모두 적용되거나 또는 전혀 적용되지 않습니다. 암시적 트랜잭션에서는 각각의 개별 SQL 문을 원자 단위로 간주합니다.
이러한 기능 때문에 SQL Server는 프로덕션 단계에서 전원 공급이 차단되거나 운영 체제가 충돌하는 등의 문제가 발생한 다음 다시 시작되고 나면 사용자의 개입 없이 자동으로 데이터베이스를 일관된 상태로 복구합니다. 이와는 대조적으로 비 관계형 시스템에서는 시스템 오류가 발생하면 데이터베이스의 일관성 문제를 검사하기 위해 오랜 시간에 걸친 수동 작업이 필요한 경우가 많습니다.
이러한 기능은 바로 트랜잭션 로그 메커니즘에 의해 제공됩니다. 트랜잭션 무결성은 SQL Server의 기본 기능, 즉 본질적인 특성으로 간주되기 때문에 로깅 설정을 해제할 수 없습니다. 특정 유틸리티를 사용하거나 빠른 BCP 또는 SELECT INTO 같은 유지 관리 작업을 통해 최소 로깅을 수행할 수 있지만 이러한 최소 로깅에서도 나중에 롤백할 수 있도록 익스텐트 할당을 기록합니다.
로깅에는 상당히 많은 공간이 필요할 수 있습니다. 예를 들어, 대개의 경우 업데이트되는 각 데이터 행의 업데이트 이전 및 이후 이미지뿐만 아니라 영향을 받는 모든 인덱스 행의 이미지도 기록해야 합니다. 로그에 기록되는 각 행에 대해 일정 양의 트랜잭션 레코드 오버헤드를 기록해야 하기 때문에 업데이트되는 데이터와 소모되는 로그 공간의 비율은 행 너비에 따라 상당히 다를 수 있습니다. 좁은 행인 경우에는 특정 UPDATE, DELETE 또는 INSERT 작업에 소모되는 로그 공간이 소모되는 데이터 공간의 10배가 될 수 있습니다. 행이 넓으면 소모되는 로그 공간이 상대적으로 작아집니다. 트랜잭션 무결성을 제공하기 위해서는 로그 공간 소모를 피할 수 없습니다. 데이터베이스 관리자는 설치 시 충분한 로그 공간을 제공해야 합니다.
필요한 로그 공간은 여러 가지 요인에 따라 달라질 수 있기 때문에 정확하게 예측하기는 어렵습니다. 처음에 로그 크기를 데이터베이스 크기의 15-30% 크기로 할당하는 것이 적절하다는 일반적인 규칙이 있지만, 실제 상황에서는 이 규칙도 매우 다를 수 있습니다. 성공적인 SQL Server 설치에서는 사용할 특정 데이터 및 응용 프로그램에 필요한 로그 공간을 대략적으로 예측하기 위해 간단한 실증적 테스트를 수행한 다음 그 예측을 바탕으로 로그 크기를 정합니다. 테스트 없이 계산만을 바탕으로 로그 크기를 정하기는 어려우며 정확하지 않을 수 있습니다.
예측하기 어려운 여러 가지 요인이 로그 공간 소모량의 변화에 영향을 미칠 수 있습니다. 그러한 요인 중 하나는 쿼리 최적화 프로그램(Query Optimizer)입니다. 특정 SQL 데이터 수정 문의 경우 액세스 계획은 데이터의 통계적 분포에 따라 시간이 가면서 달라질 수 있습니다. 액세스 계획마다 서로 다른 크기의 로그 공간을 소모할 수 있습니다. 또 따른 요인은 피할 수 없는 내부 데이터베이스 조각화로서 이는 수행되는 페이지 분할의 수에 영향을 미칠 수 있습니다. SQL Server는 사용자를 대신하여 데이터를 자동으로 관리하기 때문에 이 프로세스를 조사하거나 이 프로세스에 영향을 주기 위해 사용자가 할 수 있는 일이나 해야 할 일은 없습니다.
간단한 테스트 예제로 DBCC CHECKTABLE(syslogs)을 실행할 수 있습니다. 그러면 데이터 수정 쿼리에 대한 대표 예제를 실행하기 전과 실행한 후에 로그에 있는 2048바이트 데이터 페이지 수가 반환됩니다. 이 방법으로 이 유형의 쿼리에 필요한 로그 공간 크기를 대략적으로 알 수 있습니다. SQL Server 같은 관계형 데이터베이스에 로그 공간이나 데이터 디스크 공간을 제공할 때는 일반적으로 공간을 과다하게 할당하는 것이 최선의 방법입니다.
SQL Server 7.0 및 2000 클래스 서버는 필요에 따라 트랜잭션 로그를 확장하는 기능이 있습니다. 확장되는 크기를 사용자가 조정할 수도 있고 사용 가능한 디스크 공간을 모두 이용하도록 지정할 수도 있습니다. 로그 파일은 여러 개의 가상 로그 파일로 구성됩니다. 이 가상 로그 파일의 개수와 크기는 SQL Server에서 자동으로 결정하며 사용자가 구성할 수 없습니다. 데이터베이스를 처음 생성할 때 각 실제(Physical) 로그 파일은 최소한 두 개의 가상 로그 파일을 가집니다. 데이터베이스 관리자는 로그 공간 부족이 발생하지 않도록 하기 위해 데이터베이스의 "검사점에서 로그 자름(Truncate log on checkpoint)" 옵션을 사용 가능하게 설정하기도 합니다. 이 옵션의 목적은 주로 백업을 위해 로그 덤프에 의존하지 않는 개발용 또는 테스트용 데이터베이스를 위해 자동으로 로그를 자르는 방법을 제공하는 것입니다. 이 옵션을 설정해도 로깅 또는 트랜잭션 무결성의 설정이 해제되지는 않습니다. 이 옵션을 설정하면 단지 검사점(Checkpoint) 처리기가 약 60초 간격으로 로그를 자를 뿐입니다. "검사점에서 로그 자름(Truncate log on checkpoint)" 옵션이 설정된 데이터베이스에 수동 검사점(Checkpoint) 명령을 실행하면 로그가 잘리지 않습니다. tempdb 데이터베이스에 대해서는 이 옵션이 항상 설정되어 있지만 sp_help 저장 프로시저(stored procedure) 출력의 상태 열에는 나타나지 않습니다.
"검사점에서 로그 자름(Truncate log on checkpoint)" 옵션을 설정하더라도 여러 가지 요인에 의해 로그 공간이 부족할 수 있습니다. 이러한 각각의 요인을 아래에서 설명합니다.
대량 원자 트랜잭션, 특히 일괄적인 UPDATE, INSERT 또는 DELETE 작업 트랜잭션: 각각의 단일 SQL 문은 완전히 적용되거나 전혀 적용되지 않아야 하는 원자 단위로 간주됩니다. 따라서 모든 행 교체가 로그에 기록되어야 하며 수행되는 동안에는 트랜잭션이 잘리지 않습니다. 예를 들어, 실행하는 데 5분이 걸리는 대량 일괄 INSERT가 실행될 경우 이 시간 동안은 해당 트랜잭션에 소모되는 로그가 잘리지 않습니다. 데이터베이스 관리자는 예상되는 가장 큰 일괄 작업을 수행하는 데 충분한 로그 공간을 제공하거나 일괄 작업을 좀더 작은 그룹으로 나누어 수행해야 합니다.
커밋되지 않은 트랜잭션: 로그는 가장 오래된 커밋되지 않은 트랜잭션 이전까지만 잘립니다. 커밋되지 않은 트랜잭션의 원인은 몇 가지가 있을 수 있지만 대개는 응용 프로그램 오류가 그 원인입니다. 원인은 아래와 같습니다.
일괄 트랜잭션: 위에서 설명한 것처럼 대량 일괄 트랜잭션이 수행되는 동안에는 트랜잭션에 의해 생성되는 로그 레코드가 잘리지 않습니다. 그러나 그러한 트랜잭션 때문에 같은 기간에 커밋을 수행하는 좀더 짧은 다른 트랜잭션의 로그도 잘리지 않습니다.
예를 들어, 데이터베이스 관리자가 가능한 가장 큰 일괄 트랜잭션을 수행하는 데 충분한 크기의 로그를 할당했다고 가정합니다. 그러나 이 트랜잭션이 실행되는 동안 좀더 짧은 다른 데이터 수정 문도 역시 로그 공간을 소모할 수 있습니다. 대량 일괄 트랜잭션이 먼저 시작되었고 그것이 가장 오래된 커밋되지 않은 트랜잭션이기 때문에 로그 공간은 잘리지 않습니다. 관리자는 대량 일괄 트랜잭션의 동시성과 로그에 미치는 영향을 인식하고 적절하게 로그 크기를 조정해야 합니다.
사용자 정의 트랜잭션 내에서 사용자 입력 또는 시간이 오래 걸리는 다른 작업을 허용하는 잘못 설계된 응용 프로그램: 응용 프로그램이 BEGIN TRANSACTION을 실행한 후 사용자의 동작에 따라 시간이 오래 걸릴 수도 있는 입력을 사용자에게 요구하는 경우를 예로 들 수 있습니다. 이 경우, 사용자가 응답하고 응용 프로그램이 COMMIT을 실행할 때까지는 로그를 자를 수 없습니다.
트랜잭션이 커밋되지 않는 응용 프로그램 오류: 이 오류의 일반적인 원인은 사용자 정의 트랜잭션 내에서 DB-Library 호출 dbcancel()을 잘못 처리하는 것입니다. dbcancel()을 사용하여 쿼리를 취소할 경우 현재 실행 중인 SQL 문은 중단되고 롤백되지만 다른 트랜잭션은 계속 실행됩니다. 응용 프로그램에서 이를 인식하고 필요한 ROLLBACK TRANSACTION 또는 COMMIT TRANSACTION 문을 실행하여 트랜잭션을 닫아야 합니다. 그렇지 않으면 아래와 같은 오류 3902가 발생합니다.
The commit transaction has no corresponding BEGIN TRANSACTION.
응용 프로그램에서 SELECT @@TRANCOUNT를 보내어 어떤 트랜잭션 중첩 수준이 있는지 확인하는 것이 유용할 수도 있습니다. 그러나 응용 프로그램에서 무조건 SELECT @@TRANCOUNT를 보낸 다음 COMMIT/ROLLBACK을 실행하여 @@TRANCOUNT=0을 얻을 수는 없습니다. @@TRANCOUNT가 응용 프로그램이 예상하는 것과 다르면 응용 프로그램이 트랜잭션 중첩 수준을 추적하는 데 실패했음을 의미하므로 응용 프로그램 설계에 오류가 있는 것입니다. 이 시점에서 COMMIT/ROLLBACK을 실행하면 응용 프로그램은 어떤 트랜잭션이 의도하지 않은 트랜잭션 수준이 되었는지 알지 못하기 때문에 의도하지 않은 트랜잭션이 적용되거나 중단될 수도 있습니다. 대신에 프로그래머는 응용 프로그램 및 관련된 저장 프로시저(stored procedure)를 디버깅하여 의도하지 않은 트랜잭션 수준의 원인을 알아내야 합니다.
네트워크 연결 끊김을 SQL Server에 알리지 않는 네트워크 오류: 사용자 정의 트랜잭션이 실행되는 동안 클라이언트 워크스테이션이 응답하지 않거나 다시 부팅되거나 종료되면 네트워크 계층에서 SQL Server에 이 사실을 알려야 합니다. 네트워크가 적절하게 이 사실을 알려주지 않으면 SQL Server에게는 클라이언트가 여전히 존재하는 것처럼 보이기 때문에 클라이언트가 열어 놓은 트랜잭션이 계속 유지됩니다. 이는 네트워크 문제이므로 네트워크를 조사해야 합니다. 관리자가 sp_who, sp_lock 또는 네트워크 유틸리티를 사용하여 여전히 존재하는 클라이언트 세션을 확인한 다음 수동으로 제거함으로써 이 문제를 해결할 수 있습니다.
차단으로 인해 커밋되지 않은 트랜잭션: 다중 사용자 환경에서는 열려 있는 트랜잭션이 다른 프로세스가 설정한 잠금(Lock)에 의해 차단될 수 있습니다. 이 경우 트랜잭션이 계속 열려 있어 로그를 자르지 못하게 합니다. 이 문제를 확인하려면 프로그래머 또는 데이터베이스 관리자가 sp_who, sp_lock 또는 기타 도구를 사용하여 동시성 환경을 분석해야 합니다. 대부분의 경우 적절한 쿼리, 인덱스 및 데이터베이스 설계를 통해 차단 문제를 줄이거나 없앨 수 있습니다.
데이터 수정 쿼리를 취소하는 데 실패: 응용 프로그램이 dbcancel()을 실행했으나 네트워크 또는 SQL 문제로 인해 쿼리가 취소되지 않으면 쿼리는 계속 실행되고 트랜잭션은 열린 상태를 유지합니다. 이 문제가 의심스러우면 sp_who를 사용하여 쿼리가 취소되었는지 확인합니다. TCP/IP 소켓 클라이언트에서 취소를 시도하는 경우에는 명명된 파이프(Named Pipe) 클라이언트에서 테스트를 수행하거나 로컬 파이프를 사용하여 서버 컴퓨터에서 클라이언트 응용 프로그램을 실행합니다. 이 방법은 쿼리가 취소되지 않은 것이 네트워크 문제 때문인지 SQL 문제 때문인지 구분하는 데 도움이 됩니다.
검사점(Checkpoint) 처리기 잘라내기 대역폭 초과: 로그는 60초마다 잘리지만 이 잘라내기 속도는 제한적입니다. 이 시나리오는 일반적이지 않으므로, 먼저 기타 가능한 로그 오버플로의 원인을 고려하여 배제한 후 이 시나리오에 해당하는지 검사해야 합니다. 그러나, 많은 클라이언트가 동시에 대량 업데이트를 수행하면 최대 잘라내기 속도보다 로그가 차는 속도가 더 빠를 수 있습니다. 이는 액체를 일정한 속도로만 배출할 수 있는 깔대기를 사용할 때 배출하는 동안에도 액체가 넘칠 수 있는 것과 유사합니다. 이 시나리오에 해당하면 업데이트되는 행의 수를 줄이도록 응용 프로그램을 다시 구성할 수 있습니다. 업데이트되는 행의 수를 줄이는 것은 항상 모든 관계형 데이터베이스의 기본 설계 목표가 되어야 합니다.
이 방법으로 해결할 수 없으면 스트라이핑, 추가 컨트롤러 등을 통해 디스크 I/O 대역폭을 늘리도록 시스템을 재구성할 수 있습니다. 이 경우에는 일반적으로 검사점(Checkpoint) 처리기 프로세스가 로그 잘라내기를 계속 시도하면서 DUMP TRANSACTION 상태에서 점점 더 많은 시간을 소모하는지 확인합니다. 잘라내기 임계값을 초과하고 나면(아래 참조) 로그를 지울 때까지 검사점(Checkpoint) 처리기가 해당 데이터베이스에서 잘라내기를 시도하는 것을 보지 못할 수도 있습니다.
잘라내기 임계값 초과: 검사점(Checkpoint) 처리기는 기본적으로 DUMP TRANSACTION WITH TRUNCATE_ONLY를 수행합니다. 로그가 이미 특정 지점까지 가득 찼으면 수동으로 실행했을 때처럼 덤프가 항상 성공하지는 않습니다. 예를 들어, 검사점(Checkpoint) 처리기가 로그를 잘라낸 후 다음에 다시 잘라내기 전에 많은 업데이트 작업이 몰려 로그가 95%까지 채워질 수 있습니다. 검사점(Checkpoint) 처리기가 잘라내기를 시도할 때는 로그가 완전히 채워지지는 않았지만 거의 가득 차서 잘라내기가 불가능할 수도 있습니다. 이 때 잘라내기를 수행할 수 없는 이유는 로그 잘라내기 자체를 로그에 기록해야 하기 때문입니다. 이 경우 유일한 해결 방법은 DUMP TRANSACTION WITH NO_LOG를 사용하여 수동으로 로그를 잘라내는 것입니다. NO_LOG 옵션을 사용하는 것은 로그에 기록되지 않는 작업이기 때문에 이 작업을 하는 동안 시스템에 오류가 발생하면 데이터베이스 오류로 이어질 수 있으므로, 반드시 필요한 경우가 아니면 NO_LOG 옵션을 사용하지 않는 것이 좋습니다.
위 요인들 간 상호 작용: 예를 들어, 업데이트가 많은 환경인 경우 정상 조건에서는 검사점(Checkpoint) 처리기 잘라내기 속도에서 로그가 가득 채워지지 않습니다. 예를 들어, 잠금(Lock) 경쟁 같은 위 조건 중 하나에 의해 일시적으로 열린 트랜잭션이 로그를 50%까지 채운다고 가정하면 다른 업데이트 상황을 처리하기 위한 공간이 줄어들어 자동 잘라내기가 불가능한 지점인 잘라내기 임계값까지 도달할 가능성이 더욱 커집니다. tempdb 데이터베이스에서의 트랜잭션은 다른 데이터베이스의 경우와 마찬가지로 로그에 기록됩니다. TRUNCATE LOG ON CHECKPOINT는 tempdb에 있기 때문에 대부분의 경우 로그가 잘리고 오버플로가 발생하지 않습니다. 그러나 위에 언급한 상황으로 인해 tempdb 로그가 가득 채워질 수 있습니다. Tempdb는 일반적으로 혼합된 로그 및 데이터(sysusages.segmap=7)를 위해 구성되므로 데이터 작업과 로그 작업이 사용 가능한 동일 공간을 차지하기 위해 경쟁합니다. GROUP BY, ORDER BY DESC 등과 같은 특정 Transact-SQL 구성 요소는 작업 공간을 확보하기 위해 자동으로 tempdb를 요구합니다. 이 트랜잭션은 또한 작업 공간을 위해 tempdb에서 암시적 BEGIN TRANSACTION 레코드를 발생합니다. 이 tempdb 트랜잭션은 사용자 데이터베이스에서 트랜잭션 기간 동안 계속되며 이로 인해 이 기간 동안 tempdb 로그 잘라내기가 지연될 수 있습니다. 잠금(Lock)에 의한 차단 또는 응용 프로그램이 dbnextrow()를 완전히 처리하지 않는 등의 문제로 인해 사용자 데이터베이스에서 트랜잭션이 중단되면 tempdb에서의 트랜잭션이 열린 상태로 남게 되어 tempdb 로그 잘라내기를 방해할 수 있습니다. 프로그래머는 응용 프로그램을 디버깅하고 이 문제를 유발한 동시성 문제를 해결해야 합니다.
SQL Server 7.0 및 2000 클래스 서버에서 트랜잭션 로그 잘라내기는 가상 로그 파일(VLF)을 잘라냄으로써 수행됩니다. 활성 로그의 일부분이 특정 VLF에 있으면 해당 VLF를 잘라낼 수 없습니다. 활성 로그가 모든 VLF에 있으면 해당 로그를 잘라낼 수 없습니다. autogrowth가 사용 가능하게 설정되어 있고, 트랜잭션 로그가 있는 볼륨에 공간이 있으며 최대 파일 크기에 도달하지 않았으면 트랜잭션 로그는 로그 파일 등록 정보에 지정된 크기까지 커집니다.
아래에서는 TRUNCATE LOG ON CHECKPOINT의 설정 여부에 따라 SQL을 시작할 때의 로그 잘라내기 동작을 설명합니다.
TRUNCATE LOG ON CHECKPOINT를 설정했고 시작할 때 로그가 가득 차있으면 no_log를 사용하여 로그가 자동으로 덤프됩니다.
마스터 데이터베이스의 로그를 별도의 장치에 넣으면 로그가 로드될 수 없기 때문에 이제 마스터 데이터베이스에서는 TRUNCATE LOG ON CHECKPOINT가 기본값입니다. 가능한 옵션은 로그가 가득 찼을 때 삭제하는 것 뿐입니다.
TRUNCATE LOG ON CHECKPOINT를 설정하지 않았고 시작할 때 로그가 가득 차지 않았으면 복구는 완료되지만 최종 검사점(Checkpoint)이 작성되지 않습니다. 관리자는 데이터베이스에 액세스하여 no_truncate로 로그를 덤프하여 데이터를 저장한 다음 no_log로 덤프하여 로그를 제거하거나 덤프 작업 없이 바로 제거할 수 있습니다.
참조
SQL Server 2000에 대한 자세한 내용은 다음 서적을 참조하십시오.
Microsoft Corporation Microsoft SQL Server 7.0 System Administration Training Kit Microsoft Press, 2001
Microsoft Corporation MCSE Training Kit: Microsoft SQL Server 2000 System Administration Microsoft Press, 2001
Microsoft Corporation Microsoft SQL Server 2000 Resource Kit Microsoft Press, 2001
자세한 내용은 다음 Microsoft 교육 및 인증 과정을 참조하십시오.
Microsoft Corporation 2072 Administering a Microsoft SQL Server 2000 Database
SQL Server 7.0 이후 버전과 관련된 문제는 Microsoft 기술 자료의 다음 문서를 참조하십시오.
317375 INF: SQL Server에서 트랜잭션 로그가 예기치 않게 커지거나 가득 찬다
요약
SQL Server 트랜잭션 로그가 가득 차면 데이터베이스에서 CHECKPOINT를 비롯하여 UPDATE, DELETE 또는 INSERT 작업을 더 이상 할 수 없습니다. 이러한 상황은 아래와 같은 오류 1105로 나타납니다.
Can't allocate space for object syslogs in database dbname because the logsegment is full. If you ran out of space in syslogs, dump the transaction log. Otherwise use ALTER DATABASE or sp_extendsegment to increase the size of the segment.
master 또는 tempdb를 비롯한 어떤 데이터베이스에서나 이러한 상황이 발생할 수 있습니다. 본 문서에서는 오류 1105를 일으키는 문제점에 대한 가능한 원인과 해결 방법을 설명합니다. 트랜잭션 로그가 가득 차서 오류 1105가 발생한 경우에는 DUMP TRANSACTION 문을 사용하여 로그를 비워야 합니다. DUMP TRANSACTION의 사용 방법에 대한 자세한 내용은 SQL Server 설명서를 참조하십시오.
추가 정보
Microsoft SQL Server 같은 전형적인 관계형 데이터베이스의 기본 특성은 트랜잭션 무결성을 보장하는 것입니다. 모든 트랜잭션은 시스템 오류가 발생한 경우에도 변경 내용이 모두 적용되거나 전혀 적용되지 않는다는 점에서 완전한 원자성(즉, 개별적으로 기능을 수행)을 가져야 합니다. 사용자 정의 트랜잭션에서 BEGIN TRANSACTION 문과 COMMIT TRANSACTION 문 사이에 있는 모든 문은 모두 적용되거나 또는 전혀 적용되지 않습니다. 암시적 트랜잭션에서는 각각의 개별 SQL 문을 원자 단위로 간주합니다.
이러한 기능 때문에 SQL Server는 프로덕션 단계에서 전원 공급이 차단되거나 운영 체제가 충돌하는 등의 문제가 발생한 다음 다시 시작되고 나면 사용자의 개입 없이 자동으로 데이터베이스를 일관된 상태로 복구합니다. 이와는 대조적으로 비 관계형 시스템에서는 시스템 오류가 발생하면 데이터베이스의 일관성 문제를 검사하기 위해 오랜 시간에 걸친 수동 작업이 필요한 경우가 많습니다.
이러한 기능은 바로 트랜잭션 로그 메커니즘에 의해 제공됩니다. 트랜잭션 무결성은 SQL Server의 기본 기능, 즉 본질적인 특성으로 간주되기 때문에 로깅 설정을 해제할 수 없습니다. 특정 유틸리티를 사용하거나 빠른 BCP 또는 SELECT INTO 같은 유지 관리 작업을 통해 최소 로깅을 수행할 수 있지만 이러한 최소 로깅에서도 나중에 롤백할 수 있도록 익스텐트 할당을 기록합니다.
로깅에는 상당히 많은 공간이 필요할 수 있습니다. 예를 들어, 대개의 경우 업데이트되는 각 데이터 행의 업데이트 이전 및 이후 이미지뿐만 아니라 영향을 받는 모든 인덱스 행의 이미지도 기록해야 합니다. 로그에 기록되는 각 행에 대해 일정 양의 트랜잭션 레코드 오버헤드를 기록해야 하기 때문에 업데이트되는 데이터와 소모되는 로그 공간의 비율은 행 너비에 따라 상당히 다를 수 있습니다. 좁은 행인 경우에는 특정 UPDATE, DELETE 또는 INSERT 작업에 소모되는 로그 공간이 소모되는 데이터 공간의 10배가 될 수 있습니다. 행이 넓으면 소모되는 로그 공간이 상대적으로 작아집니다. 트랜잭션 무결성을 제공하기 위해서는 로그 공간 소모를 피할 수 없습니다. 데이터베이스 관리자는 설치 시 충분한 로그 공간을 제공해야 합니다.
필요한 로그 공간은 여러 가지 요인에 따라 달라질 수 있기 때문에 정확하게 예측하기는 어렵습니다. 처음에 로그 크기를 데이터베이스 크기의 15-30% 크기로 할당하는 것이 적절하다는 일반적인 규칙이 있지만, 실제 상황에서는 이 규칙도 매우 다를 수 있습니다. 성공적인 SQL Server 설치에서는 사용할 특정 데이터 및 응용 프로그램에 필요한 로그 공간을 대략적으로 예측하기 위해 간단한 실증적 테스트를 수행한 다음 그 예측을 바탕으로 로그 크기를 정합니다. 테스트 없이 계산만을 바탕으로 로그 크기를 정하기는 어려우며 정확하지 않을 수 있습니다.
예측하기 어려운 여러 가지 요인이 로그 공간 소모량의 변화에 영향을 미칠 수 있습니다. 그러한 요인 중 하나는 쿼리 최적화 프로그램(Query Optimizer)입니다. 특정 SQL 데이터 수정 문의 경우 액세스 계획은 데이터의 통계적 분포에 따라 시간이 가면서 달라질 수 있습니다. 액세스 계획마다 서로 다른 크기의 로그 공간을 소모할 수 있습니다. 또 따른 요인은 피할 수 없는 내부 데이터베이스 조각화로서 이는 수행되는 페이지 분할의 수에 영향을 미칠 수 있습니다. SQL Server는 사용자를 대신하여 데이터를 자동으로 관리하기 때문에 이 프로세스를 조사하거나 이 프로세스에 영향을 주기 위해 사용자가 할 수 있는 일이나 해야 할 일은 없습니다.
간단한 테스트 예제로 DBCC CHECKTABLE(syslogs)을 실행할 수 있습니다. 그러면 데이터 수정 쿼리에 대한 대표 예제를 실행하기 전과 실행한 후에 로그에 있는 2048바이트 데이터 페이지 수가 반환됩니다. 이 방법으로 이 유형의 쿼리에 필요한 로그 공간 크기를 대략적으로 알 수 있습니다. SQL Server 같은 관계형 데이터베이스에 로그 공간이나 데이터 디스크 공간을 제공할 때는 일반적으로 공간을 과다하게 할당하는 것이 최선의 방법입니다.
SQL Server 7.0 및 2000 클래스 서버는 필요에 따라 트랜잭션 로그를 확장하는 기능이 있습니다. 확장되는 크기를 사용자가 조정할 수도 있고 사용 가능한 디스크 공간을 모두 이용하도록 지정할 수도 있습니다. 로그 파일은 여러 개의 가상 로그 파일로 구성됩니다. 이 가상 로그 파일의 개수와 크기는 SQL Server에서 자동으로 결정하며 사용자가 구성할 수 없습니다. 데이터베이스를 처음 생성할 때 각 실제(Physical) 로그 파일은 최소한 두 개의 가상 로그 파일을 가집니다. 데이터베이스 관리자는 로그 공간 부족이 발생하지 않도록 하기 위해 데이터베이스의 "검사점에서 로그 자름(Truncate log on checkpoint)" 옵션을 사용 가능하게 설정하기도 합니다. 이 옵션의 목적은 주로 백업을 위해 로그 덤프에 의존하지 않는 개발용 또는 테스트용 데이터베이스를 위해 자동으로 로그를 자르는 방법을 제공하는 것입니다. 이 옵션을 설정해도 로깅 또는 트랜잭션 무결성의 설정이 해제되지는 않습니다. 이 옵션을 설정하면 단지 검사점(Checkpoint) 처리기가 약 60초 간격으로 로그를 자를 뿐입니다. "검사점에서 로그 자름(Truncate log on checkpoint)" 옵션이 설정된 데이터베이스에 수동 검사점(Checkpoint) 명령을 실행하면 로그가 잘리지 않습니다. tempdb 데이터베이스에 대해서는 이 옵션이 항상 설정되어 있지만 sp_help 저장 프로시저(stored procedure) 출력의 상태 열에는 나타나지 않습니다.
"검사점에서 로그 자름(Truncate log on checkpoint)" 옵션을 설정하더라도 여러 가지 요인에 의해 로그 공간이 부족할 수 있습니다. 이러한 각각의 요인을 아래에서 설명합니다.
대량 원자 트랜잭션, 특히 일괄적인 UPDATE, INSERT 또는 DELETE 작업 트랜잭션: 각각의 단일 SQL 문은 완전히 적용되거나 전혀 적용되지 않아야 하는 원자 단위로 간주됩니다. 따라서 모든 행 교체가 로그에 기록되어야 하며 수행되는 동안에는 트랜잭션이 잘리지 않습니다. 예를 들어, 실행하는 데 5분이 걸리는 대량 일괄 INSERT가 실행될 경우 이 시간 동안은 해당 트랜잭션에 소모되는 로그가 잘리지 않습니다. 데이터베이스 관리자는 예상되는 가장 큰 일괄 작업을 수행하는 데 충분한 로그 공간을 제공하거나 일괄 작업을 좀더 작은 그룹으로 나누어 수행해야 합니다.
커밋되지 않은 트랜잭션: 로그는 가장 오래된 커밋되지 않은 트랜잭션 이전까지만 잘립니다. 커밋되지 않은 트랜잭션의 원인은 몇 가지가 있을 수 있지만 대개는 응용 프로그램 오류가 그 원인입니다. 원인은 아래와 같습니다.
일괄 트랜잭션: 위에서 설명한 것처럼 대량 일괄 트랜잭션이 수행되는 동안에는 트랜잭션에 의해 생성되는 로그 레코드가 잘리지 않습니다. 그러나 그러한 트랜잭션 때문에 같은 기간에 커밋을 수행하는 좀더 짧은 다른 트랜잭션의 로그도 잘리지 않습니다.
예를 들어, 데이터베이스 관리자가 가능한 가장 큰 일괄 트랜잭션을 수행하는 데 충분한 크기의 로그를 할당했다고 가정합니다. 그러나 이 트랜잭션이 실행되는 동안 좀더 짧은 다른 데이터 수정 문도 역시 로그 공간을 소모할 수 있습니다. 대량 일괄 트랜잭션이 먼저 시작되었고 그것이 가장 오래된 커밋되지 않은 트랜잭션이기 때문에 로그 공간은 잘리지 않습니다. 관리자는 대량 일괄 트랜잭션의 동시성과 로그에 미치는 영향을 인식하고 적절하게 로그 크기를 조정해야 합니다.
사용자 정의 트랜잭션 내에서 사용자 입력 또는 시간이 오래 걸리는 다른 작업을 허용하는 잘못 설계된 응용 프로그램: 응용 프로그램이 BEGIN TRANSACTION을 실행한 후 사용자의 동작에 따라 시간이 오래 걸릴 수도 있는 입력을 사용자에게 요구하는 경우를 예로 들 수 있습니다. 이 경우, 사용자가 응답하고 응용 프로그램이 COMMIT을 실행할 때까지는 로그를 자를 수 없습니다.
트랜잭션이 커밋되지 않는 응용 프로그램 오류: 이 오류의 일반적인 원인은 사용자 정의 트랜잭션 내에서 DB-Library 호출 dbcancel()을 잘못 처리하는 것입니다. dbcancel()을 사용하여 쿼리를 취소할 경우 현재 실행 중인 SQL 문은 중단되고 롤백되지만 다른 트랜잭션은 계속 실행됩니다. 응용 프로그램에서 이를 인식하고 필요한 ROLLBACK TRANSACTION 또는 COMMIT TRANSACTION 문을 실행하여 트랜잭션을 닫아야 합니다. 그렇지 않으면 아래와 같은 오류 3902가 발생합니다.
The commit transaction has no corresponding BEGIN TRANSACTION.
응용 프로그램에서 SELECT @@TRANCOUNT를 보내어 어떤 트랜잭션 중첩 수준이 있는지 확인하는 것이 유용할 수도 있습니다. 그러나 응용 프로그램에서 무조건 SELECT @@TRANCOUNT를 보낸 다음 COMMIT/ROLLBACK을 실행하여 @@TRANCOUNT=0을 얻을 수는 없습니다. @@TRANCOUNT가 응용 프로그램이 예상하는 것과 다르면 응용 프로그램이 트랜잭션 중첩 수준을 추적하는 데 실패했음을 의미하므로 응용 프로그램 설계에 오류가 있는 것입니다. 이 시점에서 COMMIT/ROLLBACK을 실행하면 응용 프로그램은 어떤 트랜잭션이 의도하지 않은 트랜잭션 수준이 되었는지 알지 못하기 때문에 의도하지 않은 트랜잭션이 적용되거나 중단될 수도 있습니다. 대신에 프로그래머는 응용 프로그램 및 관련된 저장 프로시저(stored procedure)를 디버깅하여 의도하지 않은 트랜잭션 수준의 원인을 알아내야 합니다.
네트워크 연결 끊김을 SQL Server에 알리지 않는 네트워크 오류: 사용자 정의 트랜잭션이 실행되는 동안 클라이언트 워크스테이션이 응답하지 않거나 다시 부팅되거나 종료되면 네트워크 계층에서 SQL Server에 이 사실을 알려야 합니다. 네트워크가 적절하게 이 사실을 알려주지 않으면 SQL Server에게는 클라이언트가 여전히 존재하는 것처럼 보이기 때문에 클라이언트가 열어 놓은 트랜잭션이 계속 유지됩니다. 이는 네트워크 문제이므로 네트워크를 조사해야 합니다. 관리자가 sp_who, sp_lock 또는 네트워크 유틸리티를 사용하여 여전히 존재하는 클라이언트 세션을 확인한 다음 수동으로 제거함으로써 이 문제를 해결할 수 있습니다.
차단으로 인해 커밋되지 않은 트랜잭션: 다중 사용자 환경에서는 열려 있는 트랜잭션이 다른 프로세스가 설정한 잠금(Lock)에 의해 차단될 수 있습니다. 이 경우 트랜잭션이 계속 열려 있어 로그를 자르지 못하게 합니다. 이 문제를 확인하려면 프로그래머 또는 데이터베이스 관리자가 sp_who, sp_lock 또는 기타 도구를 사용하여 동시성 환경을 분석해야 합니다. 대부분의 경우 적절한 쿼리, 인덱스 및 데이터베이스 설계를 통해 차단 문제를 줄이거나 없앨 수 있습니다.
데이터 수정 쿼리를 취소하는 데 실패: 응용 프로그램이 dbcancel()을 실행했으나 네트워크 또는 SQL 문제로 인해 쿼리가 취소되지 않으면 쿼리는 계속 실행되고 트랜잭션은 열린 상태를 유지합니다. 이 문제가 의심스러우면 sp_who를 사용하여 쿼리가 취소되었는지 확인합니다. TCP/IP 소켓 클라이언트에서 취소를 시도하는 경우에는 명명된 파이프(Named Pipe) 클라이언트에서 테스트를 수행하거나 로컬 파이프를 사용하여 서버 컴퓨터에서 클라이언트 응용 프로그램을 실행합니다. 이 방법은 쿼리가 취소되지 않은 것이 네트워크 문제 때문인지 SQL 문제 때문인지 구분하는 데 도움이 됩니다.
검사점(Checkpoint) 처리기 잘라내기 대역폭 초과: 로그는 60초마다 잘리지만 이 잘라내기 속도는 제한적입니다. 이 시나리오는 일반적이지 않으므로, 먼저 기타 가능한 로그 오버플로의 원인을 고려하여 배제한 후 이 시나리오에 해당하는지 검사해야 합니다. 그러나, 많은 클라이언트가 동시에 대량 업데이트를 수행하면 최대 잘라내기 속도보다 로그가 차는 속도가 더 빠를 수 있습니다. 이는 액체를 일정한 속도로만 배출할 수 있는 깔대기를 사용할 때 배출하는 동안에도 액체가 넘칠 수 있는 것과 유사합니다. 이 시나리오에 해당하면 업데이트되는 행의 수를 줄이도록 응용 프로그램을 다시 구성할 수 있습니다. 업데이트되는 행의 수를 줄이는 것은 항상 모든 관계형 데이터베이스의 기본 설계 목표가 되어야 합니다.
이 방법으로 해결할 수 없으면 스트라이핑, 추가 컨트롤러 등을 통해 디스크 I/O 대역폭을 늘리도록 시스템을 재구성할 수 있습니다. 이 경우에는 일반적으로 검사점(Checkpoint) 처리기 프로세스가 로그 잘라내기를 계속 시도하면서 DUMP TRANSACTION 상태에서 점점 더 많은 시간을 소모하는지 확인합니다. 잘라내기 임계값을 초과하고 나면(아래 참조) 로그를 지울 때까지 검사점(Checkpoint) 처리기가 해당 데이터베이스에서 잘라내기를 시도하는 것을 보지 못할 수도 있습니다.
잘라내기 임계값 초과: 검사점(Checkpoint) 처리기는 기본적으로 DUMP TRANSACTION WITH TRUNCATE_ONLY를 수행합니다. 로그가 이미 특정 지점까지 가득 찼으면 수동으로 실행했을 때처럼 덤프가 항상 성공하지는 않습니다. 예를 들어, 검사점(Checkpoint) 처리기가 로그를 잘라낸 후 다음에 다시 잘라내기 전에 많은 업데이트 작업이 몰려 로그가 95%까지 채워질 수 있습니다. 검사점(Checkpoint) 처리기가 잘라내기를 시도할 때는 로그가 완전히 채워지지는 않았지만 거의 가득 차서 잘라내기가 불가능할 수도 있습니다. 이 때 잘라내기를 수행할 수 없는 이유는 로그 잘라내기 자체를 로그에 기록해야 하기 때문입니다. 이 경우 유일한 해결 방법은 DUMP TRANSACTION WITH NO_LOG를 사용하여 수동으로 로그를 잘라내는 것입니다. NO_LOG 옵션을 사용하는 것은 로그에 기록되지 않는 작업이기 때문에 이 작업을 하는 동안 시스템에 오류가 발생하면 데이터베이스 오류로 이어질 수 있으므로, 반드시 필요한 경우가 아니면 NO_LOG 옵션을 사용하지 않는 것이 좋습니다.
위 요인들 간 상호 작용: 예를 들어, 업데이트가 많은 환경인 경우 정상 조건에서는 검사점(Checkpoint) 처리기 잘라내기 속도에서 로그가 가득 채워지지 않습니다. 예를 들어, 잠금(Lock) 경쟁 같은 위 조건 중 하나에 의해 일시적으로 열린 트랜잭션이 로그를 50%까지 채운다고 가정하면 다른 업데이트 상황을 처리하기 위한 공간이 줄어들어 자동 잘라내기가 불가능한 지점인 잘라내기 임계값까지 도달할 가능성이 더욱 커집니다. tempdb 데이터베이스에서의 트랜잭션은 다른 데이터베이스의 경우와 마찬가지로 로그에 기록됩니다. TRUNCATE LOG ON CHECKPOINT는 tempdb에 있기 때문에 대부분의 경우 로그가 잘리고 오버플로가 발생하지 않습니다. 그러나 위에 언급한 상황으로 인해 tempdb 로그가 가득 채워질 수 있습니다. Tempdb는 일반적으로 혼합된 로그 및 데이터(sysusages.segmap=7)를 위해 구성되므로 데이터 작업과 로그 작업이 사용 가능한 동일 공간을 차지하기 위해 경쟁합니다. GROUP BY, ORDER BY DESC 등과 같은 특정 Transact-SQL 구성 요소는 작업 공간을 확보하기 위해 자동으로 tempdb를 요구합니다. 이 트랜잭션은 또한 작업 공간을 위해 tempdb에서 암시적 BEGIN TRANSACTION 레코드를 발생합니다. 이 tempdb 트랜잭션은 사용자 데이터베이스에서 트랜잭션 기간 동안 계속되며 이로 인해 이 기간 동안 tempdb 로그 잘라내기가 지연될 수 있습니다. 잠금(Lock)에 의한 차단 또는 응용 프로그램이 dbnextrow()를 완전히 처리하지 않는 등의 문제로 인해 사용자 데이터베이스에서 트랜잭션이 중단되면 tempdb에서의 트랜잭션이 열린 상태로 남게 되어 tempdb 로그 잘라내기를 방해할 수 있습니다. 프로그래머는 응용 프로그램을 디버깅하고 이 문제를 유발한 동시성 문제를 해결해야 합니다.
SQL Server 7.0 및 2000 클래스 서버에서 트랜잭션 로그 잘라내기는 가상 로그 파일(VLF)을 잘라냄으로써 수행됩니다. 활성 로그의 일부분이 특정 VLF에 있으면 해당 VLF를 잘라낼 수 없습니다. 활성 로그가 모든 VLF에 있으면 해당 로그를 잘라낼 수 없습니다. autogrowth가 사용 가능하게 설정되어 있고, 트랜잭션 로그가 있는 볼륨에 공간이 있으며 최대 파일 크기에 도달하지 않았으면 트랜잭션 로그는 로그 파일 등록 정보에 지정된 크기까지 커집니다.
아래에서는 TRUNCATE LOG ON CHECKPOINT의 설정 여부에 따라 SQL을 시작할 때의 로그 잘라내기 동작을 설명합니다.
TRUNCATE LOG ON CHECKPOINT를 설정했고 시작할 때 로그가 가득 차있으면 no_log를 사용하여 로그가 자동으로 덤프됩니다.
마스터 데이터베이스의 로그를 별도의 장치에 넣으면 로그가 로드될 수 없기 때문에 이제 마스터 데이터베이스에서는 TRUNCATE LOG ON CHECKPOINT가 기본값입니다. 가능한 옵션은 로그가 가득 찼을 때 삭제하는 것 뿐입니다.
TRUNCATE LOG ON CHECKPOINT를 설정하지 않았고 시작할 때 로그가 가득 차지 않았으면 복구는 완료되지만 최종 검사점(Checkpoint)이 작성되지 않습니다. 관리자는 데이터베이스에 액세스하여 no_truncate로 로그를 덤프하여 데이터를 저장한 다음 no_log로 덤프하여 로그를 제거하거나 덤프 작업 없이 바로 제거할 수 있습니다.
참조
SQL Server 2000에 대한 자세한 내용은 다음 서적을 참조하십시오.
Microsoft Corporation Microsoft SQL Server 7.0 System Administration Training Kit Microsoft Press, 2001
Microsoft Corporation MCSE Training Kit: Microsoft SQL Server 2000 System Administration Microsoft Press, 2001
Microsoft Corporation Microsoft SQL Server 2000 Resource Kit Microsoft Press, 2001
자세한 내용은 다음 Microsoft 교육 및 인증 과정을 참조하십시오.
Microsoft Corporation 2072 Administering a Microsoft SQL Server 2000 Database
SQL Server 7.0 이후 버전과 관련된 문제는 Microsoft 기술 자료의 다음 문서를 참조하십시오.
317375 INF: SQL Server에서 트랜잭션 로그가 예기치 않게 커지거나 가득 찬다