[MS-SQL] DB 로그삭제(파일축소) - Version 4 일괄적용

 

DB 로그삭제(파일축소) - Version 4 일괄적용

 

---

포스팅 개요

서버의 모든 DB 정보를 불러오는 'sys.databases' 테이블을 참고하여 모든 사용자 DB의 로그 삭제(축소) 작업을 일괄로 진행하는 쿼리입니다. 그럼 오늘 포스팅 예제 쿼리의 목적과 장점에 대해 먼저 알아보겠습니다.

• 트랜잭션 로그(transaction log)의 한계까지 가득 차 에러가 생길 때 사용합니다.
• 하드디스크 용량이 부족할 정도로 로그 크기가 커졌을 때 사용합니다.
• Sql Server 2005 이상을 기준으로 작성되었습니다.
• 아래 그림은 시스템 데이터베이스와 사용자 데이터베이스를 구분하는 방법입니다.

시스템과 사용자 데이터베이스의 구분 방법

 

---

예제 쿼리

Version 1 부터 꾸준히 보완하였고 사용자 DB만 불러와 일괄로 처리하도록 완성하였습니다. 'sys.databases' 테이블의 컬럼 중에 database_id > 4 조건을 주면 사용자 DB 정보만 불러옵니다.

USE [master];

DECLARE @SqlStatement as nvarchar(max)
DECLARE @DBName VARCHAR(50)
DECLARE @State_Desc VARCHAR(50)
DECLARE @SEQ INT
	
SET @SEQ = 0

DECLARE cur CURSOR 
FOR
    SELECT NAME,state_desc FROM sys.databases WHERE database_id > 4 AND REPLACE(NAME,'.','') = NAME ORDER BY NAME
    
OPEN cur

FETCH NEXT FROM cur INTO @DBName, @state_desc

WHILE (@@FETCH_STATUS = 0)
BEGIN
    SELECT @DBName, @state_desc
    
    EXEC ('ALTER DATABASE ' + @DBName + ' SET RECOVERY SIMPLE')
	EXEC ('SELECT [name], [recovery_model_desc] FROM ' + @DBName + '.sys.databases WHERE [name] = ''' + @DBName + '''')
	SET @SqlStatement = 'DECLARE @LogFileLogicalName as SYSNAME;'
	SET @SqlStatement = @SqlStatement + 'SELECT @LogFileLogicalName = [Name] FROM ' + @DBName + '.sys.database_files WHERE type = 1;'
	SET @SqlStatement = @SqlStatement + 'DBCC Shrinkfile(@LogFileLogicalName, 1)'
	EXEC ('USE '+ @DBName + ';' + @SqlStatement )
	EXEC ('ALTER DATABASE ' + @DBName + ' SET RECOVERY FULL')
	EXEC ('SELECT [name], [recovery_model_desc] FROM ' + @DBName + '.sys.databases WHERE [name] = ''' + @DBName + '''')
	EXEC ('SELECT * FROM ' + @DBName + '.sys.database_files')

	SET @SEQ = @SEQ + 1

	PRINT @DBName + ' 완료. 현재 ' + CAST(@SEQ AS VARCHAR) + '건'

    FETCH NEXT FROM cur INTO @DBName, @state_desc
END

CLOSE cur
DEALLOCATE cur

---

실행 결과

한번에 일괄로 모든 데이터베이스를 정리하기에 로그 삭제(축소) 쿼리의 완성형이라고 생각합니다. 실제로 사용 중인 쿼리이며 주로 스케줄에 등록하여 사용하고 있습니다.

쿼리 실행 결과창

쿼리 실행 메시지창

---

참고 링크

 

2017.12.15 - [DataBase/MS-SQL] - [MS-SQL] DB 로그삭제(파일축소) - Version 1 단일적용(일반)

[MS-SQL] DB 로그삭제(파일축소) - Version 1 단일적용(일반)

2022.12.10 - [DataBase/MS-SQL] - [MS-SQL] DB 로그삭제(파일축소) - Version 2 단일적용(간단1)

[MS-SQL] DB 로그삭제(파일축소) - Version 2 단일적용(간단1)

2022.12.22 - [DataBase/MS-SQL] - [MS-SQL] DB 로그삭제(파일축소) - Version 3 단일적용(간단2)

[MS-SQL] DB 로그삭제(파일축소) - Version 3 단일적용(간단2)