☰

SQL Server 로그 공간 사용이 어떻게 100%를 초과할 수 있습니까?

tag-icon • tag-icon ms-sql
SQL Server 로그 공간 사용이 어떻게 100%를 초과할 수 있습니까?

트랜잭션 로그를 보고하는 애플리케이션 오류(예외)가 표시됩니다."ACTIVE_TRANSACTION으로 인해 가득 찼습니다.". 에서 보고한 보류 중인 거래가 없습니다 DBCC OPENTRAN.

실행 중 DBCC SQLPERF('logspace')로그 크기는 1.3MB에 불과하지만 사용된 로그 공간은 107.7%를 보고하는 것을 확인했습니다.

이 데이터베이스는 2TB 이상의 로그 파일 최대 크기로 구성되어 있으며, 초기 크기는 2MB, 자동 증가가 10%로 설정되어 있습니다. 복구 모델이 단순으로 설정되어 있습니다.

사용된 로그 공간이 어떻게 100%를 초과할 수 있으며, 이렇게 많은 공간을 사용할 수 있는데 왜 예외가 생성됩니까?

답변1

범인을 찾아보세요

당신은 달릴 수 있습니다sp_WhoIsActive누가 무엇을 실행하고 있는지 확인하고 실행 중인 프로세스/활성 트랜잭션의 논리를 살펴보세요. T-SQL 쿼리를 더 빠르게 또는 더 작은 트랜잭션으로 실행하도록 최적화하여 재사용을 위해 로그 파일에서 사용되지 않은 여유 공간을 더 빨리 해제할 수 있는지 확인하세요.

설계상 가능합니다

SQL Server 단순 복구 모델

모든 트랜잭션은 여전히 ​​트랜잭션 로그에 기록되지만 일단 트랜잭션이 완료되고 데이터가 데이터 파일에 기록되면 트랜잭션 로그 파일에 사용된 공간을 이제 새 트랜잭션에서 재사용할 수 있습니다.

원천

복구 모델 설정 에서 장기 실행 트랜잭션으로 인해 트랜잭션 로그가 엄청나게 커지는 경우를 본 적이 있습니다 SIMPLE. 해당 트랜잭션은 실제로 실패했으며 롤백하는 데 시간이 오래 걸렸습니다. 따라서 오랫동안 실행되는 트랜잭션, 성능이 좋지 않거나 최적화되지 않은 잘못 작성된 쿼리로 인해 이 문제가 발생할 수 있습니다.

공간이 할당되면SIMPLE다음 중 하나를 사용하여 복구 모델 데이터베이스 트랜잭션 로그 파일사용되지 않은 트랜잭션 로그 여유 공간또는OS 수준 여유 공간의 자동 증가당, 트랜잭션 로그 파일은 파일 축소 작업이 발생할 때까지 새 공간을 유지합니다 DBCC SHRINKFILE (database_log, 2048).

중요한:예를 들어 파일 축소 작업이 발생하면 DBCC SHRINKFILE (database_log, 2048)트랜잭션 로그 내에서 사용되지 않은 로그 공간만 OS에 여유 공간으로 해제됩니다. 파일 축소 작업 중에는 트랜잭션 로그에 기록된 활성 실행 중인 트랜잭션이 해제되지 않습니다.

로그 파일 축소

로그 파일을 축소할 때의 문제는 다음에 대규모 트랜잭션이나 잘못 작성된 쿼리가 실행될 때 로그 파일이 다시 채워지고 축소 작업을 반복해야 한다는 것입니다. 이 문제를 영구적으로 해결하려면 근본 문제를 찾아서 해결하십시오. 그동안 로그 파일을 계속해서 축소하세요.

근본 원인을 해결하세요

근본적인 문제는 쿼리일 가능성이 높으므로 누가 무엇을 하고 있는지 파악하고 그들에게 연락하고 발견한 문제를 보고하면 서버 디스크 공간 파티션을 낭비하지 않도록 논리를 수정하라는 압력을 받게 됩니다. 성능 조정을 쿼리하기 위한 논리 최적화를 살펴보세요.

  • 근본 원인 추적

    때로는 근본 원인이 장기간 실행되는 트랜잭션이 아닐 수도 있습니다. 예를 들어 누군가가 복제를 설정했지만 제대로 다시 해제하지 않았기 때문일 수 있습니다. 확인부터 시작하세요log_reuse_wait_desc in sys.databases:

    SELECT name, log_reuse_wait_desc FROM sys.databases;

    하지만 이는 지금 당장 로그를 줄일 수 없는 이유를 보여주는 스냅샷일 뿐이라는 점을 명심하세요.

    그런 다음 거기에서 흥미로운 것을 발견하지 못하면 다음을 수행할 수 있습니다.sp_WhoIsActive를 테이블에 기록 사람들이 언제 행동하는지 파악 BEGIN TRAN하고 몇 시간 동안 세션을 열어 두세요. 장기 실행 트랜잭션을 찾아 소유자와 대화하고 하나의 거대한 트랜잭션 대신 작은 덩어리로 작업을 수행할 수 있는지 확인하십시오.

  • 로그 파일 공간 메타데이터

    DBCC SQLPERF(logspace)데이터베이스 로그 파일 사용에만 관심이 있는 경우에는 절대적으로 기능하는 명령입니다. SQL Server 인스턴스의 각 데이터베이스에 대한 각 로그 파일의 누적 크기와 소비된 공간의 양(총 로그 파일 크기의 백분율)을 제공합니다. 단점은 결과가 데이터베이스에 대한 집계라는 사실입니다. 로그 파일이 여러 개인 경우 결과는 파일 수준이 아닌 데이터베이스 수준에서 표시됩니다.

    이 DBCC 명령은 부적절한 로그 백업 일정 또는 잘못된 로그 파일 크기로 인해 발생하는 문제를 검토할 때 유용하지만 로그 파일 크기 조정, 백업 일정 빈도 조정에 대한 정보를 바탕으로 결정을 내리는 데 필요한 모든 정보를 제공하지는 않습니다. 또는 복구 모델.

    원천

관련 정보