[MSSQL] DB 로그 파일 축소 스크립트 - 주기적으로 실행되는 스케줄로 만들기

바로 이전 DB 포스팅에서 MS-SQL의 DB 로그 파일을 축소하는 쿼리에 대해서 알아보았습니다.

 

그런데 이 로그 파일이라는 것이 매 번 사용자들이 DB에 접근할 때마다 기록되는 것이다 보니, 한 번 로그 파일을 축소 했다고 해서 영원히 줄어드는 것이 아닙니다. 파일 축소 후에 몇일 몇 주가 지난 후, 로그 파일 용량이 급속도로 증가하는 것을 볼 수 있습니다.

 

그러면 매번 관리자가 로그 파일들의 용량을 확인해서 축소 시켜주어야 하는데, 여간 번거로운 일이 아닙니다.

 

그렇다면 이러한 번거로운 작업들을 자동화 시킬 수는 없을까요?

 

과거 아래 작성했던 2개의 포스팅 내용을 결합하면 그 문제를 해결 할 수 있습니다.

 

- 포스팅 참고하기 -

1) DB 로그 파일 축소 스크립트 : http://ozit.tistory.com/103 

2) DB 자동 백업 설정하기 (SQL Server Agent) : http://ozit.tistory.com/86

 

DB 로그 파일 축소 스크립트를 SQL Server Agent 에 등록하여 정기적으로 로그를 축소시키도록 하는 것입니다.

 

그러면 바로 한 번 알아보도록 하겠습니다.^^

 

이번 포스팅은 DB 로그 파일 축소 스크립트와 SQL Server Agent에 대한 자세한 설명은 생략하고, 오직 DB 로그 파일 축소 쿼리를 스케줄로 등록시키는 것에 대한 내용만 포함합니다. 스크립트에 대한 이해 및 SQL Server Agent 에 대해 조금이라도 더 자세한 정보를 알기 원하신다면 위의 2 포스팅을 참조해주세요.^^

 

 

 

1) Microsoft SQL Server Management Studio 에서 SQL Server 에이전트 새작업을 선택합니다.

 * SQL Server 에이전트 트리 메뉴에서 노란 [작업] 폴더 위에서 마우스 우측 버튼 클릭하면 컨텍스트 메뉴가 뜨는데, 여기서 [새 작업]을 선택합니다.

 

 

 

 

 

2) 새 작업을 작성합니다.

 * 일반 페이지의 화면입니다. 작업의 이름과 설명을 입력합니다. 설명은 입력하지 않아도 무방합니다.

 

 

 

 

3) 단계 를 설정합니다.

 * 단계 페이지의 하단에 [새로 만들기] 버튼을 클릭하면 아래와 같이 새 작업 단계 화면이 뜹니다.

 * 단계 이름을 입력합니다.

 * 로그 축소할 파일의 데이터베이스를 [데이터베이스(D):] 선택박스에서 선택합니다. 그리고  로그 파일을 축소하는 스크립트를 하단 입력창에 입력합니다. (위의 첫번째 참고 포스팅에는 해당 스크립트의 동작에 대한 자세한 설명이 포함되어 있습니다.)

 

 

 

USE [@@DB_NAME@@] -- 여기에 DB이름을 입력합니다. 데이터베이스(D): 에서 선택한 DB 이름을 입력하면 됩니다.

GO

-- Declare variables
DECLARE @SqlStatement as nvarchar(max)
DECLARE @LogFileLogicalName as sysname

 

-- Alter the database to simple recovery
SET @SqlStatement = 'ALTER DATABASE ' + DB_NAME() + ' SET RECOVERY SIMPLE'
EXEC ( @SqlStatement )

 

-- Make sure it has been altered
SELECT [name], [recovery_model_desc] FROM sys.databases WHERE [name] = DB_NAME()

 

-- Set the log file name variable
SELECT @LogFileLogicalName = [Name] FROM sys.database_files WHERE type = 1

 

-- Shrink the logfile
DBCC Shrinkfile(@LogFileLogicalName, 1)

 

-- Alter the database back to FULL
SET @SqlStatement = 'ALTER DATABASE ' + DB_NAME() + ' SET RECOVERY FULL'
EXEC ( @SqlStatement )

 

-- Make sure it has been changed back to full
SET @SqlStatement = 'SELECT [name], [recovery_model_desc] FROM ' + DB_NAME() + '.sys.databases WHERE [name] = ''' + DB_NAME() + ''''
EXEC ( @SqlStatement )

 

 

 

 

4) 일정 을 설정합니다.

 * 일정 페이지의 하단에 [새로 만들기] 버튼을 클릭하면 아래와 같이 새 작업 일정 화면이 뜹니다.

 * 일정 이름을 입력합니다.

 * 일정은 매일, 주간, 격주, 월간 등 다양하게 설정 가능하며, 시간대, 시간 간격 되풀이 등 도 설정할 수 있습니다. 저는 아래 화면에서 보시는 것처럼 매 2주마다 일요일 오전 2시에 위의 스크립트가 실행되도록 설정하였습니다.

 

 

 

 

 

5) 모두 확인을 누르고, 작업 등록을 완료합니다. 그리고 정상 동작하는지 테스트합니다.

 다시 SQL Server 에이전트의 작업 목록으로 가보시면 방금 등록한 작업 스케줄이 등록된 것을 확인할 수 있습니다. (만약 보이지 않는다면 새로 고침하시면 나옵니다.)

 스케줄은 격주로 매 일요일 새벽 2시에 동작하도록 해 놓았는데, 이 스케줄의 스크립트에 문제가 있는지 여부를 그 때까지 기다릴 수 없습니다. 새벽 2시에 정상 동작하는지 확인하기는 어렵죠?^^

 그래서 컨텍스트 메뉴에서 - [작업 시작] 메뉴를 선택하면 스케줄 일정에 상관없이 작업을 즉시 실행시킵니다. 이를 통해 작성한 SQL 쿼리가 올바르게 동작하는지 여부를 확인할 수 있습니다.

 

 

 

아래의 화면에서 보듯이 작업이 정상적으로 수행된 것을 볼 수 있습니다.

 

 

그러면 실제 해당 DB의 로그 파일이 정상적으로 축소되었는지 확인해 봐야 되지 않을까요?

 

 

위의 파일은 스크립트에 로그 파일 축소를 위해 등록한 DB 파일로 작업 전에 500여MB 였던 로그 파일이, 에이전트 작업 완료 후, 1MB로 축소된 것을 확인할 수 있습니다. 날짜도 축소를 하여 파일이 수정되었으므로 수정된 날짜(2월 3일)로 변경되었습니다.

 

SQL 에이전트는 이번 포스팅도 그렇고, 이 전에 작성한 포스팅도 그렇고, 스크립트로 작성할 수 있는 모든 작업들을 스케줄로 등록하여 관리할 수 있어 여러모로 유용합니다. 다양한 작업들을 응용하여 등록하고 사용해 보세요^^