트랜잭션 로그 줄이기 (출처:http://www.sqlworld.pe.kr)

출처 :

http://www.sqlworld.pe.kr

1. 테스트를 위한 임시 테이블 만들기와 데이터 추가하기

예전의 테스트 처럼 sqlworld 데이터베이스를 이용하여 실제 상황을 재현해 보도록 하겠습니다. 현재 sqlworld 데이터베이스의 옵션은 다음과 같습니다.

o 데이터베이스 복구모델 : 최대
o 자동 축소기능 : 사용안함

다음 [그림 1]은 현재 sqlworld의 데이터베이스와 로그 사이즈를 보여줍니다.

[그림 1]

우선 현재의 sqlworld 데이터베이스를 백업 받도록 하겠습니다.(모든 백업의 기본은 전체 백업입니다)

USE Master GO BACKUP DATABASE sqlworld TO DISK = 'E:\Data\sqlworld.bak'

1) 테이블 만들기

다음과 같이 테스트 테이블 Test_Log를 만들도록 하겠습니다.

USE sqlworld GO CREATE TABLE Test_Log ( col1 int PRIMARY KEY, col2 char(1000) )

다음과 같이 스크립트를 이용해서 레코드를 추가하도록 하겠습니다.

SET NOCOUNT ON GO DECLARE @num int SET @num = 1 WHILE @num < 5001 BEGIN INSERT INTO Test_Log VALUES(@num, REPLICATE('A',1000)) SET @num = @num + 1 END

위 작업을 통해서 오천개 정도의 레코드가 Test_Log 테이블에 추가되었습니다. 이로 인하여 sqlworld 데이터 사이즈와 로그 사이즈가 증가했습니다. 아래 [그림 2]가 그 내용을 확인해주고 있습니다.

[그림2]

만일 sqlworld 데이터베이스 복구 모델이 '단순' 이었다면 위와 같은 작업이 완료가 되면 로그가 자동으로 제거되어 [그림 2] 처럼 로그 사이즈가 증가하지 않을 것입니다.(이 내용이 잘 이해되지 않으면 바로 이전의 강좌를 다시한번 읽어보시기 바랍니다)

2) 데이터를 변경하여 데이터와 로그 사이즈 증가시키기

다음과 같이 데이터를 변경하고 삭제하여 로그 사이즈를 증가시켜 보겠습니다.

UPDATE Test_Log Set col2 = REPLICATE('B',1000) WHERE col1 < 1000 GO DELETE FROM Test_Log WHERE col1 < 2000 GO DELETE FROM Test_Log WHERE col1 < 4000

위 작업을 수행하니 Test_Log 테이블의 데이터 변경에 대한 로그와삭제에 대한 로그가 증가함에 따라 sqlworld 데이터베이스의 사이즈는 다음 [그림 3]과 같이 변하였습니다.

[그림3]

로그 파일은 0.99 MB에서 6.74 MB 로 사이즈가 증가함을 알 수 있습니다.

이처럼 트랜잭션이 발생함에 따라 로그가 계속 쌓이면서 로그 파일의 사이즈가 계속 증가함을 알 수 있습니다. 이제 확인할 내용은 로그 백업을 하면 로그가 줄어드는지, 그리고 로그 파일의 사이즈는 어떻게 되는지 하는 것입니다.

2. 트랜잭션 로그 백업을 통한 로그 줄이기

로그 백업은 예전에 배운 데이터베이스 백업과 비슷한 방법으로 이루어 집니다. EM을 통해서 할 수도 있고 QA에서 직접 BACKUP LOG 문을 이용하여 백업 받을 수도 있습니다. 이 테스트에서는 QA를 이용하여 현재 sqlworld 데이터베이스의 로그를 백업받도록 하겠습니다.

USE Master GO BACKUP LOG sqlworld TO DISK = 'E:\Data\sqlworld_log.bak'

수행하는 방법은 BACKUP DATABASE 문과 비슷합니다.

만일 로그를 백업 받지는 않고 단지 현재의 로그만 지우고 싶으면 다름과 같이 TRUNCATE_ONLY 옵션을 사용하면 됩니다.

USE Master GO BACKUP LOG WITH TRUNCATE_ONLY

이렇게 로그를 백업 받은 후의 sqlworld 데이터베이스의 사이즈를 보니 다음 [그림 4]와 같이 변한 것을 볼 수 있습니다.

[그림4]

로그 사이즈가 3.64 MB 에서 1.84 MB로 줄어 들었습니다. 하지만 로그 파일의 사이즈는 아직도 6.74 MB로서 원래 사이즈를 그대로 유지하고 있습니다. 결국 4.9 MB의 빈 공간이 놀고(?) 있게 됩니다.

3. DBCC SHRINKDATABASE을 이용한 로그파일 사이즈 줄이기

DBCC SHRINKDATABASE 을 이용해서 수작업으로 비어있는 로그 사이즈를 없애서 로그 파일의 사이즈를 축소해 보도록 하겠습니다.

USE Master GO DBCC SHRINKDATABASE(Sqlworld)

결과는 다음 [그림 5]와 같이 변한것을 알 수 있습니다.

[그림5]

로그 파일의 사이즈가 [그림 4]와 비교 할 때 6.74 MB에서 2.49 MB로 줄어든 것을 알 수 있습니다. DBCC SHRINKDATABASE에 의해 줄어드는 로그 파일의 사이즈는 실제 데이터 파일의 빈 공간이 많을 수록 효과가 좋습니다. DBCC SHRINKDATABASE 또는 DBCC SHRINKFILE에 대한 내용은 이전의 데이터베이스에 대한 강좌를 참고해 주시기 바랍니다.

4. 정리

로그 파일 사이즈 문제를 가지고 질문을 하시는 경우가 상당히 많습니다. 지금까지 설명드린 내용이 도움이 되었으면 합니다. 데이터베이스 관리자는 항상 로그 파일에 대한 관심을 가지고 모니터링을 해야 합니다. 하드디스크의 여유 공간은 충분한지도 수시로 확인하셔야 합니다. 그렇지 않으면 TempDB가 꽉 찼다는 등의 오류가 발생하게 됩니다.



exec sp_helpdb dbname
backup log dbname with truncate_only
DBCC shrinkfile(dbname_log  ,5)???????????