2007~2011/SQL Server2011. 4. 19. 18:28

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

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


-- 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 
저작자표시비영리변경금지
Posted by 사용자 Lai Go
TAG ,
Trackback 0 Comment 4

댓글을 달아 주세요

  1. 왕초보

    안녕하세요.
    블로그 강좌 잘 보고 있습니다.
    제가 초보라 궁금
    DB에 있는 테이블을 초기화 및 DB 용량 까지 초기화 하는 방법좀 가르쳐 주세요?

    지금 쓰는 방법은 2가지 사용하고 있습니다.
    그런데, 테이블은 초기화 되는데. 용량이 초기화 않됨..
    아시면 가르쳐 주세요.

    1)truncate table test <- test 테이블 그대로 두고 내용만 제거
    2)dbcc shrinkdatabase (testdb) <- testdb 용량 크기 작게됨, 그런데 DB 생성시 초기화 크기가 않됨

    부탁합니다.

    2012.01.17 14:22 [ ADDR : EDIT/ DEL : REPLY ]
    • 사용자 Lai Go

      데이터 파일 처음 크기를 변경하는 방법을 문의하시는 것인가요?
      다음 쿼리를 통해 Testdb 의 Testdata 파일 크기를 100MB 로 변경할 수 있습니다.
      ALTER DATABASE [Testdb] MODIFY FILE ( NAME =
      N'Testdata', SIZE = 102400KB )
      GO
      궁금사항이 있으시면 http://sqler.com 을 이용해 주세요~ 고맙습니다 ^^;

      2012.01.17 14:26 신고 [ ADDR : EDIT/ DEL ]
  2. 왕초보

    Lai Go 님
    답변 고맙습니다.
    - 건강 조심하세요. -

    2012.01.18 12:04 [ ADDR : EDIT/ DEL : REPLY ]