[SQL] MS-SQL DB 로그 파일 축소하기

http://ooz.co.kr/103

MS-SQL은 실제 데이터가 저장된 데이터베이스 파일(*.mdf)과 데이터베이스의 처리에 대한 로그 파일(*.ldf)로 구성되어 데이터를 관리합니다.

mdf 파일에는 현재 최종적인 데이터의 정보를 가지고 있는 실 데이터를 관리하는 데이터 파일이며,
ldf는 데이터 변화에 따른 로그를 기록 관리 하는 파일입니다. ldf 파일은 데이터 복원에 있어서 중요한 파일이며, 이번 포스팅에서는 이 ldf 파일의 용량을 줄이는 방법에 대해서 설명합니다.

여러 데이터베이스를 관리하다 보면 몇몇 ldf 파일의 크기가 상당히 커지는 경우가 발생합니다.
해당 DB의 접근이 많아 트랜잭션 로그가 지속적으로 또 급격히 쌓이거나 하는 경우입니다. ldf 파일을 서버 관리자가 정기적으로 별도의 백업을 하지 않거나 또는 정기 백업 시스템에 문제가 생겨서 ldf 파일이 제대로 관리되지 않는다면 (또 용량 제한을 두지 않는다면) 무한히 커지게 될 것입니다.

사실 용량이 커지는 것 자체로 문제가 되는 것은 아니지만,
로그 파일 용량이 한계치에 도달하거나 서버의 HDD를 모두 써버릴 만큼 용량이 커지게 되면 해당 DB 또는 같은 서버에 존재하는 모든 DB로의 접근이 실패하거나 문제가 발생할 수 있습니다.


먼저 DB서버에서 더 이상 데이터를 쓸 수 없을 만큼 용량이 한계치에 도달하게 되면 SELECT를 제외한 모든 DML(Data Manipulation Language)의 처리(INSERT, UPDATE 등)가 실패하기 됩니다.
그 이유는 데이터에 변화를 주는 조작이 있게 되면 로그 파일에 기록을 해야 하는데, 더 이상 기록을 할 수 있는 공간이 없기 때문입니다. SELECT 동작은 별도의 로그 파일에 기록하지 않기 때문에 용량이 꽉찬 DB서버에서도 정상적으로 동작합니다.
웹사이트를 서비스하고 있는 경우, 사용자가 로그인을 시도하는 경우, DB에 로그인 히스토리를 기록하는 처리 등을 수행한다면 해당 웹 사이트에 더 이상 사용자가 로그인하지 못하게 됩니다.

그러면 별도의 백업을 한 다음, 또는 백업이 필요없다 판단되어 ldf 파일의 용량을 초기화 시키고 싶을 경우가 있습니다. (ldf 파일을 초기화 시켜도 실 데이터베이스 파일(mdf) 에 영향을 미치는 것은 아니므로, 현재 mdf 데이터만 제대로 유지하면 된다면 ldf 파일 용량 축소 시키는 작업은 DB데이터나 서비스에 문제가 되지는 않습니다.)

그러한 경우에는 다음의 Transaction-SQL로 작성된 쿼리를 실행시키시면 되겠습니다.
코드 출처 : http://ottoradke.com

간단히 아래 코드의 YourDatabaseName 이라고 된 부분에 로그 파일을 축소할 DB이름을 입려한 후, 쿼리를 실행(F5)하면 됩니다.

------------------------------------------------------------------------------
-- Otto R. Radke - http://ottoradke.com
-- Info: T-SQL script to shrink a database's transaction log. Just set the
-- database name below and run the script and it will shrink the
-- transaction log.
------------------------------------------------------------------------------
------------------------------------------------------------------------------
-- Update the line below with the name of the database who's transaction
-- log you want to shrink.
------------------------------------------------------------------------------
1) USE YourDatabaseName
------------------------------------------------------------------------------
-- Don't change anything below this line.
------------------------------------------------------------------------------
GO
-- Declare variables
DECLARE @SqlStatement as nvarchar(max)
DECLARE @LogFileLogicalName as sysname

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

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

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

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

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

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

1) USE YourDatabaseName
 - 해당 데이터베이스(YourDatabaseName)에 대해 쿼리를 수행할 것을 지정합니다.

2) SET @SqlStatement = 'ALTER DATABASE ' + DB_NAME() + ' SET RECOVERY SIMPLE'
EXEC ( @SqlStatement )
 - DB 복구 모델을 Simple Recovery 로 변경합니다. 다시 말하면 해당 DB의 복구 모델을 단순 복구 모델(Simple) 형태로 전환하는 것인데, MS-SQL에서는 아래의 3가지 복구 모델을 지원합니다.

 1. 전체 복구 모델 - Full Recovery Model
 2. 대량 로그 복구 모델 - Bulked-Log Recovery Model
 3. 단순 복구 모델 - Simple Recovery Model  

DB를 단순 복구 모델로 설정하게 되면 더 이상 해당 DB는 트랜잭션 로그를 기록하지 않고, MS-SQL에 로그 파일 관리가 위임되어 관리자가 직접 로그 파일(ldf)을 접근하여 관리할 수 없게 됩니다.

 * 참고) DB_NAME ( [ database_id ] ) : DB_NAME()은 인자로 Database_Id를 지정하지 않으면 현재 설정된 DB의 이름을 가져옵니다. 여기서는 YourDatabaseName으로 설정된 DB 이름을 반환합니다.

3) SELECT [name], [recovery_model_desc] FROM sys.databases WHERE [name] = DB_NAME()
 - 2)번 단계에서 DB의 복구 모델을 Simple로 변경하였는데, 이 모델이 정상적으로 반영되었는지, 조회하는 것입니다. SELECT하였기 때문에 화면에 변경된 모델이 아래와 같이 출력됩니다.

* SIMPLE 모델로 정상적으로 변경되었습니다.

4) SELECT @LogFileLogicalName = [Name] FROM sys.database_files WHERE type = 1
 - 축소할 로그 파일의 논리적 이름을 가져옵니다. 데이터베이스 파일은 물리적 파일과 연결된 논리적 파일 명을 가지고 있으며, 이는 데이터베이스 속성에서 파일 탭에서 확인할 수 있습니다.
로그 파일 또한 논리적 파일명을 가지고 있고, 이 논리적 파일이 물리적인 파일과 연결되어 있는 것을 알 수 있습니다.

5) DBCC Shrinkfile(@LogFileLogicalName, 1)
  - DB 로그 파일의 용량을 줄이는 명령어를 실행합니다.
   첫번째 인자 : 용량을 축소시킬 로그 파일의 논리적인 이름. 4)번 단계에서 이름을 가져 왔습니다.
   두번째 인자 : 축소시킬 용량(MB)입니다. 1MB로 축소시킵니다.

DBCC SHRINKFILE
     ( { file_name | file_id }
         { [ , target_size ]
             | [ , { EMPTYFILE | NOTRUNCATE | TRUNCATEONLY } ]
        }
    )
 

 Shrinkfile 명령어가 실행되면 다음과 같은 처리 결과 Row가 표시됩니다.

 * DbId : 축소할 파일의 데이터베이스 ID 번호
 * FileId : 축소할 파일의 파일 ID 번호
 * CurrentSize : 현재 파일이 차지하고 있는 8KB 페이지의 수
 * MinimumSize : 파일이 최소한으로 차지할 수 있는 8KB 페이지의 수. 이것은 파일의 최소 크기나 원래 만들어진 크기와 일치합니다.
 * UsedPages 현재 파일에서 사용되는 8KB 페이지의 수
 * EstimatedPages SQL Server에서 예상하는 파일 축소 가능 크기에 해당하는 8KB 페이지의 수

참고로 Shrinkfile 명령어는 Log파일 외에 실제 DB파일에도 사용할 수 있습니다.
DBCC는 MS-SQL의 Transaction-SQL 프로그래밍에서 사용되는 일련의 데이터베이스 콘솔 명령어 집합이며, 좀 더 자세한 정보는 아래 링크에서 확인하실 수 있습니다.
DBCC 링크 : http://en.wikipedia.org/wiki/Database_Console_Commands_(Transact-SQL)


6) SET @SqlStatement = 'ALTER DATABASE ' + DB_NAME() + ' SET RECOVERY FULL'
EXEC ( @SqlStatement )
 - DB 복구 모델을 다시 Full Recovery(전체 복구) 형태로 전환합니다.
 - 전체 복구 모델은 MS-SQL 의 기본 값(제가 테스트한 2008버전에서는!)이며, 기존처럼 트랜잭션 로그를 ldf 파일에 기록하는 형태의 모델입니다. DB의 데이터에 변화를 준 모든 이벤트에 대해 모두 기록하는 형태의 로그입니다.

7) SET @SqlStatement = 'SELECT [name], [recovery_model_desc] FROM ' + DB_NAME() + '.sys.databases WHERE [name] = ''' + DB_NAME() + ''''
EXEC ( @SqlStatement )
 - 6)번 단계에서 DB의 복구 모델을 다시 Full로 변경하였는데, 이 모델이 정상적으로 반영되었는지, 조회하는 것입니다. SELECT하였기 때문에 화면에 변경된 모델이 아래와 같이 출력됩니다.

생각보다 로그 축소 코드는 간단하며, 어려운 내용 또한 없습니다.
그리고 길지 않은 코드 이지만, 이중에서도 DB복구 모델을 변경한다던지, 로그를 출력한다던지 하는 코드를 제외한 실제 DB 로그를 축소시키는 핵심 코드는 4),5)번 코드 뿐입니다. (그와중에도 4번은 로그 파일의 논리 이름을 가져오는 역할만 합니다.)


그리고 마지막으로 중요한 것은!!
로그 파일을 축소시키게 되면 더 이상 로그 파일을 통해 트랜잭션 히스토리 추적으로 DB복구가 불가능하게 되므로 로그 파일 축소 전에 DB 전체 백업하는 것을 권장합니다. (아니, 반드시 하세요!)