트랜잭션 로그 Full(오류: 9002)

트랜잭션 로그 파일이 가득 찼을 경우, 내용 확인과 조치 방법

SQL Server는 ACID 속성 유지를 위해 '미리 쓰기 로그(Write-Ahead Log)'를 사용하고 트랜잭션 로그에 먼저 기록을 해야 트랜잭션이 시작되는 구조라서 트랜잭션 로그가 가득 차면 쿼리가 실행되지 않는다.
http://technet.microsoft.com/ko-kr/library/ms186259(v=sql.105).aspx

트랜잭션 로그 파일이 더 이상 커지지 못하는 상황에서 로그파일이 가득 차면 어떤 오류가 발생하는지 확인하였다.
오류: 9002, 심각도: 17, 상태: 4.
The transaction log for database 'Northwind' is full.
To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases
데이터베이스 'Northwind'의 트랜잭션 로그가 꽉 찼습니다.
로그의 공간을 다시 사용할 수 없는 이유를 확인하려면 sys.databases의 log_reuse_wait_desc 열을 참조하십시오.

**TechNet에 기술되어 있는 조치방법**
From <http://technet.microsoft.com/ko-kr/library/ms175495(v=sql.105).aspx>
트랜잭션 로그가 꽉 찬 경우의 대처 방법으로 다음 방법을 사용할 수도 있습니다.

• 로그 백업
• 로그가 자동으로 증가될 수 있도록 디스크 공간 확보
• 공간이 충분한 디스크 드라이브로 로그 파일 이동
• 로그 파일의 크기 증가
• 다른 디스크에 로그 파일 추가
• 장기 실행 트랜잭션 완료 또는 중지

조치 방법

@트랜잭션 로그파일 사용률 확인

DBCC SQLPERF(LOGINFO)

1. 백업에 필요한 디스크 공간이 충분한 경우
a. 로그 백업
-- 트랜잭션 로그 백업
BACKUP LOG Northwind TO DISK = N'F:\Northwind.trn' WITH STATS=5, COMPRESSION
GO

2. 디스크 여유공간이 없는 경우
a. 로그 백업
아래 방법으로 트랜잭션 로그 백업 작업을 실행하면 트랜잭션 로그 파일 없이 백업만 실행한다. 트랜잭션 로그 파일을 사용한 시점 복원 시나리오가 필요하지 않은 경우에 사용하자.
-- 트랜잭션 로그 백업 (SQL Server 2008 이상)
BACKUP LOG Northwind TO DISK = N'nul';
GO
-- 트랜잭션 로그 백업 (SQL Server 2005 이하)
BACKUP LOG Northwind WITH TRUNCATE_ONLY
GO

b. 복구모델을 '단순'으로 변경
WITH NO_WAIT 옵션 사용시 주의 (트랜잭션이 자체적으로 커밋되거나 롤백되기를 기다리지 않고 요청된 데이터베이스 상태 또는 옵션 변경을 즉시 완료할 수 없는 경우에 요청이 실패하도록 지정)
USE master
GO
-- 복구모델을 단순으로 변경하면 트랜잭션 로그파일이 비워진다.
-- 서비스 온라인 상태에서 작업이 가능하다.
ALTER DATABASE Northwind SET RECOVERY SIMPLE WITH NO_WAIT
GO
-- 작업 후 로그 사용률 확인
DBCC SQLPERF(LOGSPACE)
-- 복구모델을 다시 전체모드로 전환한다.
ALTER DATABASE Northwind SET RECOVERY FULL WITH NO_WAIT
GO

트랜잭션 로그를 비우더라도 트랜잭션 로그 파일 (*.ldf) 파일의 크기는 줄어들지 않는다.
파일의 크기를 줄이려면 트랜잭션 로그 파일에 대한 SHRINKFILE을 따로 실행해야 한다.
MSSQL 파일 축소 - SHRINKFILE (tistory.com)