MSSQL 로그파일(LDF) 사이즈 줄이기

TEST 한 MSSQL 버전은 2005 이지만 2008 2012 등 다른 버전도 명령어는 같거나 유사합니다.

MSSQL 로그파일(LDF) 사이즈 줄이기

1. SQL Log 정보 확인

 DBCC SqlPerf(Logspace)

테스터 할 DB 명은 House  입니다. 현자 로그파일 사이즈는 3089.867MB이고 대부분 사용하고 있습니다.

2. 아래 2가지 방법중 하나는 선택하여 로그 여유 공간은 최소화합니다.

 

 방법 1: 트랜잭션 로그 파일 최소의 단위로 축소
   Backup Log [House] With Truncate_Only

 방법 2: 트랜잭션 로그파일 삭제
    Backup Log [House] With No_Log

3. 로그 파일 사이즈를 원하는 용량으로 줄인다.

  DBCC Shrinkfile(house_log,10)

PS.실운영 서버의 경우 적당한 용량으로 로그사이즈를 설정하세요.
   너무 작게 설정할경우 빈번한 로그사이즈 증가로 일시적으로
   성능이 저하 될 수 있습니다.

-- 여러가지 이유로 3번의 명령으로 로그 사이즈가 줄여지지 않는경우가 있습니다.
   위와 같은 경우 DBCC Shrinkfile 명령어를 실행하기전 데이터베이스 복구모델을
   Simple로 설정 후 ShrinkFile 명령어를 사용하면 해결됩니다.

Simple 모드 설정 방법

-- 명령문
Alter DataBase House
Set Recovery Simple

-- GUI 모드

추가로 ShrinkFile 대신 아래 명령으로 %단위로 조정 할 수도 있습니다.
Dbcc Shrinkdatabase(house,10%)

마지막으로 위 그래픽 작업을 스케쥴에 등록하기 위한 스크립트 공유 합니다.

USE DB명

GO

Create Table #SP_HelpFile(              name  varchar(50) null,

  fileid int null,

  filename  varchar(200)         null,

  filegroup varchar(20) null,

  size varchar(20) null,

  maxsize varchar(20) null,

  growth varchar(20) null,

  usage varchar(20) null)

insert into #SP_HelpFile

EXEC SP_HELPFILE 

if exists(select * from #SP_HelpFile

where dbo.Explode(size,' ',1) > 205214528

  and fileid = 2)

  begin

-- Truncate the log by changing the database recovery model to SIMPLE.

ALTER DATABASE DB명

SET RECOVERY SIMPLE

-- 로그파일을 102400으로 축소

DBCC SHRINKFILE (DB명_Log, 102400);

-- Reset the database recovery model.

ALTER DATABASE DB명

SET RECOVERY FULL;

    end

-----------------------------------------------------------------------------

위에 문자열 split 하기 위해 생성한 함수 (explode)는 아래와 같습니다.

CREATE FUNCTION [dbo].[Explode]  

(  

    @ORG_STR  NVarchar(Max), -- 쪼갤 원문  

    @SPLITTER NVARCHAR(1),   -- 쪼갤 문자열  

    @Bungi   int  

)  

RETURNS Varchar(Max)  

AS  

BEGIN  

    -- 변수 선언  

    DECLARE @START INT,  

            @FOUND INT;  

 SET @FOUND = CHARINDEX(@SPLITTER, @ORG_STR, 1);  

 Declare @Str Varchar(Max), @Snum int  

 Select @Str= ''    

    -- 원문이 빈 문자열이면 그대로 리턴  

    IF ISNULL(DATALENGTH(@ORG_STR), 0) = 0 or @FOUND < 1  

    Begin  

   IF @Bungi = 1  

    Select @Str = @ORG_STR  

    Else   

    Select @Str= ''    

       GOTO FUNC_RET;  

    End  

   Else  

    Begin  

       Set @ORG_STR = @ORG_STR+ @SPLITTER  

    End  

    -- 변수 초기화     

    Select @START = 1, @Snum = 1;   

    SET @FOUND = CHARINDEX(@SPLITTER, @ORG_STR, @START + 1);  

    -- @SPLITTER 가 존재하는 경우에만 LOOP  

    WHILE @FOUND >= @START  

    BEGIN  

  IF @Bungi = @Snum  

     Begin  

   Select @Str = SUBSTRING(@ORG_STR, @START, @FOUND - @START)  

   GOTO FUNC_RET;  

     End  

        -- 다음 @SPLITTER 찾음  

        Select @START = @FOUND + 1, @Snum = @Snum + 1  

        SET @FOUND = CHARINDEX(@SPLITTER, @ORG_STR, @START);  

    END;  

   

FUNC_RET:   

   

 Return (@Str)  

  

END