트랜잭션 로그 파일 제거 방법

트랜잭션 로그 파일이 여러 개로 구성되어 있는 환경에서 필요하지 않아 지워야 할 때가 있습니다. 물론 사용 중인 로그 파일은 지워지지 않겠죠. 트랜잭션 로그 파일을 제거하는 방법에 대해서 참고자료를 바탕으로 구성하여 아래와 같이 정리하였습니다. 

-- 1. 신규 데이터베이스를 생성하며 트랜잭션 로그 3개 만듭니다. 

CREATE DATABASE LAIGO 

ON  

PRIMARY  

 ( NAME = N'LAIGO', FILENAME = N'D:\SQLDATA\LAIGO.mdf' , SIZE = 30MB , FILEGROWTH = 1MB ) 

LOG ON  

 (NAME = N'LAIGO_log2', FILENAME = N'D:\SQLDATA\LAIGO_log2.ldf', SIZE = 3MB, FILEGROWTH = 1MB) 

,(NAME = N'LAIGO_log3', FILENAME = N'D:\SQLDATA\LAIGO_log3.ldf', SIZE = 3MB, FILEGROWTH = 1MB) 

,(NAME = N'LAIGO_log4', FILENAME = N'D:\SQLDATA\LAIGO_log4.ldf', SIZE = 3MB, FILEGROWTH = 1MB) 

GO 

-- 2. 복구 모델을 FULL 로 설정하고 백업을 통해 데이터베이스를 백업하여 로그가 기록되도록 합니다.

ALTER DATABASE LAIGO SET RECOVERY FULL 

BACKUP DATABASE LAIGO TO DISK = 'D:\SQLDATA\LAIGO.bak' WITH INIT 

-- 3. 샘플 데이터를 채웁니다.

USE LAIGO
GO

CREATE TABLE TBL_DATA
(
 A INT,
 B CHAR(10),
 C CHAR(100)
)
 
SET NOCOUNT ON
GO

DECLARE @count INT
SET @count = 1

WHILE @count < 10000
BEGIN
 INSERT INTO TBL_DATA VALUES(@count, convert(char(10), @count), REPLICATE('A',100))
 SET @count = @count + 1
 INSERT INTO TBL_DATA VALUES(@count, convert(char(10), @count), REPLICATE('B',100))
 SET @count = @count + 1
 INSERT INTO TBL_DATA VALUES(@count, convert(char(10), @count), REPLICATE('C',100))
 SET @count = @count + 1
END

 


-- 4. 트랜잭션 로그가 8MB 까지 늘어났고 로그 사용량도 60% 를 넘었습니다.

DBCC SQLPERF(logspace)

-- 5. VLF 상태를 확인해 보니 4 Log file 은 Status = 0 으로 사용중이지 않습니다.

DBCC LOGINFO(LAIGO)

-- 6. 사용하지 않는 트랜잭션 로그 파일 LAIGO_log4 를 제거합니다. 삭제는 성공합니다.

ALTER DATABASE LAIGO REMOVE FILE LAIGO_log4

-- 7. 트랜잭션 로그 백업  

BACKUP LOG LAIGO TO DISK = 'D:\SQLDATA\LAIGO.bak'

Processed 375 pages for database 'LAIGO', file 'LAIGO_log2' on file 2.
Processed 272 pages for database 'LAIGO', file 'LAIGO_log3' on file 2.
BACKUP LOG successfully processed 647 pages in 0.195 seconds (25.888 MB/sec).

-- 8. LAIGO_log3 파일은 아직 사용 중입니다.

DBCC LOGINFO(LAIGO)

-- 9. 사용 중인 LAIGO_log3 을 제거해 봅니다만 역시 예상대로 실패합니다. 

ALTER DATABASE LAIGO REMOVE FILE LAIGO_log3

Msg 5042, Level 16, State 2, Line 1
The file 'LAIGO_log3' cannot be removed because it is not empty.

-- 10. LAIGO_log3 파일을 shrikfile 작업을 수행하여 축소한 뒤, 백업을 수행합니다. 

USE LAIGO 

DBCC SHRINKFILE(LAIGO_log3) 

USE master

BACKUP LOG LAIGO TO DISK = 'D:\SQLDATA\LAIGO.bak'

-- 11. LAIGO_log3 이 더 이상 사용하지 않는 상태로 확인됩니다.

DBCC LOGINFO(LAIGO) 

-- 12. LAIGO_log3 로그를 제거합니다.

ALTER DATABASE LAIGO REMOVE FILE LAIGO_log3 

 

The file 'LAIGO_log3' has been removed.

-- 13. 데이터베이스 파일 상태를 확인합니다. 제거한 파일이 offline 으로 나타나며 Log 를 백업하게 되면 해당 목록에서 삭제되게 됩니다. 

SELECT * FROM LAIGO.sys.database_files 

-- 14. 마지막 남아 있는 LAIGO_log2 도 삭제해 보면 어떨까요? 마지막 남은 Primary 데이터 또는 로그는 삭제가 되지 않는다는 메시지를 볼 수 있습니다. 경로를 옮겨야 한다면 DB 오프라인 후 MODIFY FILE 을 적용하면 되겠네요...

ALTER DATABASE LAIGO REMOVE FILE LAIGO_log2 

Msg 5020, Level 16, State 1, Line 1
The primary data or log file cannot be removed from a database.

[참고자료]

Remove transaction log files 

http://sqlblog.com/blogs/tibor_karaszi/archive/2009/06/17/remove-transaction-log-files.aspx


작성자 : Lai Go / 작성일자 : 2011.04.21