트랜잭션 로그 파일이 여러 개로 구성되어 있는 환경에서 필요하지 않아 지워야 할 때가 있습니다. 물론 사용 중인 로그 파일은 지워지지 않겠죠. 트랜잭션 로그 파일을 제거하는 방법에 대해서 참고자료를 바탕으로 구성하여 아래와 같이 정리하였습니다.
-- 3. 샘플 데이터를 채웁니다.
-- 4. 트랜잭션 로그가 8MB 까지 늘어났고 로그 사용량도 60% 를 넘었습니다.

-- 7. 트랜잭션 로그 백업
-- 8. LAIGO_log3 파일은 아직 사용 중입니다.

-- 9. 사용 중인 LAIGO_log3 을 제거해 봅니다만 역시 예상대로 실패합니다.
-- 10. LAIGO_log3 파일을 shrikfile 작업을 수행하여 축소한 뒤, 백업을 수행합니다.
-- 11. LAIGO_log3 이 더 이상 사용하지 않는 상태로 확인됩니다.

-- 12. LAIGO_log3 로그를 제거합니다.
-- 13. 데이터베이스 파일 상태를 확인합니다. 제거한 파일이 offline 으로 나타나며 Log 를 백업하게 되면 해당 목록에서 삭제되게 됩니다.
-- 1. 신규 데이터베이스를 생성하며 트랜잭션 로그 3개 만듭니다.
-- 2. 복구 모델을 FULL 로 설정하고 백업을 통해 데이터베이스를 백업하여 로그가 기록되도록 합니다.
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
GO
CREATE TABLE TBL_DATA
(
A INT,
B CHAR(10),
C CHAR(100)
)
SET NOCOUNT ON
GO
DECLARE @count INT
SET @count = 1
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
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 으로 사용중이지 않습니다.

-- 6. 사용하지 않는 트랜잭션 로그 파일 LAIGO_log4 를 제거합니다. 삭제는 성공합니다.
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).
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.
The file 'LAIGO_log3' cannot be removed because it is not empty.
-- 10. LAIGO_log3 파일을 shrikfile 작업을 수행하여 축소한 뒤, 백업을 수행합니다.
USE LAIGO
DBCC SHRINKFILE(LAIGO_log3)
USE master
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
작성자 : Lai Go / 작성일자 : 2011.04.21
댓글을 달아 주세요
안녕하세요.
2012.01.17 14:22 [ ADDR : EDIT/ DEL : REPLY ]블로그 강좌 잘 보고 있습니다.
제가 초보라 궁금
DB에 있는 테이블을 초기화 및 DB 용량 까지 초기화 하는 방법좀 가르쳐 주세요?
지금 쓰는 방법은 2가지 사용하고 있습니다.
그런데, 테이블은 초기화 되는데. 용량이 초기화 않됨..
아시면 가르쳐 주세요.
1)truncate table test <- test 테이블 그대로 두고 내용만 제거
2)dbcc shrinkdatabase (testdb) <- testdb 용량 크기 작게됨, 그런데 DB 생성시 초기화 크기가 않됨
부탁합니다.
데이터 파일 처음 크기를 변경하는 방법을 문의하시는 것인가요?
2012.01.17 14:26 신고 [ ADDR : EDIT/ DEL ]다음 쿼리를 통해 Testdb 의 Testdata 파일 크기를 100MB 로 변경할 수 있습니다.
ALTER DATABASE [Testdb] MODIFY FILE ( NAME =
N'Testdata', SIZE = 102400KB )
GO
궁금사항이 있으시면 http://sqler.com 을 이용해 주세요~ 고맙습니다 ^^;
Lai Go 님
2012.01.18 12:04 [ ADDR : EDIT/ DEL : REPLY ]답변 고맙습니다.
- 건강 조심하세요. -
고맙습니다. 새해 복 많이 받으세요~ ^^
2012.01.18 14:57 신고 [ ADDR : EDIT/ DEL ]