사랑하기 위해 필요한 것들

SQL Server 2005에서 DBCC SHRINKFILE 문을 사용하여 트랜잭션 로그 파일을 축소하는 방법

SQL Server 2005에서는 축소 작업(DBCC SHRINKFILE)이 지정한 트랜잭션 로그 파일을 요청된 크기로 즉시 축소하려고 합니다. 전체 복구 모델에서 트랜잭션 로그 파일을 수동으로 축소하려면 먼저 트랜잭션 로그 파일을 백업한 다음 DBCC SHRINKFILE 문을 사용하여 트랜잭션 로그 파일을 축소하십시오.

일반적으로 SQL Server 2005에서 트랜잭션 로그 파일을 축소하는 것은 SQL Server 2000에서 트랜잭션 로그 파일을 축소하는 것보다 빠릅니다. 이는 SQL Server 2005 로그 관리자가 실제 디스크 저장 장치 순서에 따라 비활성 가상 로그 파일을 만들거나 다시 사용하기 때문입니다. 따라서 트랜잭션 로그 파일의 비활성 부분은 대개 파일의 끝에 있습니다.

예를 들어, 트랜잭션 로그 파일에 100개의 가상 로그 파일이 있을 수 있고 2개의 가상 로그 파일만 사용되는 경우 SQL Server 2000은 첫 번째로 사용된 가상 로그 파일을 트랜잭션 로그 파일의 시작 부분에 저장하고 두 번째로 사용된 가상 로그 파일을 트랜잭션 로그 파일의 중간 부분에 저장합니다. 트랜잭션 로그 파일을 두 개의 가상 로그 파일로만 축소하기 위해 SQL Server는 더미 로그 항목을 사용하여 두 번째 가상 로그 파일의 나머지 부분을 채웁니다. SQL Server는 논리 로그의 시작 부분을 로그 관리자가 지정하는 사용 가능한 다음 가상 로그 파일로 이동합니다. 로그 관리자는 마지막 활성 가상 로그 파일 바로 앞에 있는 트랜잭션 로그 파일의 중간 부분에 가상 로그 파일을 만들 수 있습니다. 이 경우 트랜잭션 로그 파일을 두 개의 가상 로그 파일로 축소하기 위해 여러 개의 로그 백업 작업과 로그 축소 작업을 사용해야 합니다. 최악의 경우 트랜잭션 로그 파일을 두 개의 가상 로그 파일로 축소하기 위해 각각 50개씩의 로그 백업 작업과 로그 축소 작업을 사용해야 할 수도 있습니다.

그러나 SQL Server 2005에서는 하나의 DBCC SHRINKFILE 문을 사용하여 트랜잭션 로그 파일을 즉시 두 개의 가상 로그 파일로 축소할 수 있습니다. 이는 SQL Server 2005 로그 관리자가 실제 디스크 저장 장치 순서에 따라 두 개의 가상 로그 파일을 만들기 때문입니다. 이 두 개의 가상 로그 파일은 모두 트랜잭션 로그 파일의 시작 부분에 저장됩니다.

SQL Server 2005에서 여유 공간이 거의 없는 트랜잭션 로그 파일을 축소하려는 경우 추가 로그 백업 작업을 수행해야 할 수 있습니다. 추가 로그 백업 작업을 수행하면 트랜잭션 로그 파일이 더 작은 크기로 잘립니다. 이 로그 백업 작업은 SQL Server 2000에서 트랜잭션 로그 파일의 축소를 위해 수행하는 세 단계 이외에 추가로 수행하는 작업입니다. 자세한 내용은 "소개" 절에서 언급한 Microsoft 기술 자료 문서를 참조하십시오. SQL Server 2005에서 여유 공간이 거의 없는 트랜잭션 로그 파일을 축소하려면 다음과 같이 하십시오.

1. 트랜잭션 로그 파일을 백업하여 대부분의 활성 가상 로그 파일을 비활성화합니다. 이렇게 하면 나중에 비활성 가상 로그 파일을 제거할 수 있습니다. 이렇게 하려면 다음 Transact-SQL 문과 유사한 Transact-SQL 문을 실행하십시오.

   BACKUP LOG <DatabaseName> TO DISK = '<BackupFile>'

   참고 이 문에서 <DatabaseName>은 백업할 데이터베이스 이름의 자리 표시자이고, <BackupFile>은 백업 파일의 전체 경로에 대한 자리 표시자입니다.
   
   예를 들어, 다음 Transact-SQL 문을 실행하십시오.

   BACKUP LOG TestDB TO DISK='C:\TestDB1.bak'

2. 트랜잭션 로그 파일을 축소합니다. 이렇게 하려면 다음 Transact-SQL 문과 유사한 Transact-SQL 문을 실행하십시오.

   DBCC SHRINKFILE (<FileName>, <TargetSize>) WITH NO_INFOMSGS

   참고 이 문에서 <FileName>은 트랜잭션 로그 파일 이름의 자리 표시자이고, <TargetSize>는 트랜잭션 로그 파일의 대상 크기에 대한 자리 표시자입니다. 대상 크기는 합리적이어야 합니다. 예를 들어, 두 개의 가상 로그 파일보다 작은 크기로 트랜잭션 로그 파일을 축소할 수는 없습니다.
3. DBCC SHRINKFILE 문이 트랜잭션 로그 파일을 대상 크기로 축소하지 않을 경우 1단계에서 언급한 BACKUP LOG 문을 실행하여 가상 로그 파일을 추가로 비활성화합니다.
4. 2단계에서 언급한 DBCC SHRINKFILE 문을 실행합니다. 이 작업을 수행하고 나면 트랜잭션 로그 파일이 대상 크기와 비슷해집니다.

요약하면 SQL Server 2005에서는 다음 가상 로그 파일을 선택하는 로그 관리자의 알고리즘이 변경되었습니다. 따라서 SQL Server 2005에서 트랜잭션 로그 파일을 축소하는 방법이 SQL Server 2000에서 트랜잭션 로그 파일을 축소하는 방법과 다를 수 있습니다.

• 로그 파일에 여유 공간이 많으면 SQL Server 2005에서 트랜잭션 로그 파일을 축소하는 것이 SQL Server 2000에서 트랜잭션 로그 파일을 축소하는 것보다 빠릅니다.
• 로그 파일에 여유 공간이 없으면 SQL Server 2005에서 트랜잭션 로그 파일을 축소하는 것과 SQL Server 2000에서 트랜잭션 로그 파일을 축소하는 것이 같습니다.
• 로그 파일에 여유 공간이 거의 없으면 SQL Server 2000에서 수행해야 하는 것보다 더 많은 추가 로그 백업 작업을 SQL Server 2005에서 수행해야 합니다.

SQL Server 2000에서 DBCC SHRINKFILE을 사용하여 트랜잭션 로그를 축소하는 방법

DBCC SHRINKFILE을 실행할 때 SQL Server는 먼저 가상 로그 파일을 제거하여 로그 파일을 축소합니다. 대상 파일 크기로 축소되지 않았으면 SQL Server는 가상 로그가 채워질 때까지 마지막 가상 로그 파일에 더미(Dummy) 로그 항목을 넣고 로그의 윗 부분을 파일의 시작 위치로 옮깁니다. 그런 다음 트랜잭션 로그를 축소하는 작업을 완료하기 위해 아래와 같은 작업이 필요합니다.

• 로그의 활성 부분을 비우기 위해 BACKUP LOG 문을 실행합니다.
• 로그 파일이 대상 크기로 줄어들 때까지 원하는 대상 크기를 사용하여 DBCC SHRINKFILE을 다시 실행합니다.

아래 예제에서는 pubs 데이터베이스를 사용할 때 이 방법을 사용하여 pubs_log 파일을 2MB로 축소하는 단계를 보여줍니다.

1. DBCC SHRINKFILE(pubs_log, 2)을 실행합니다.
2. 대상 크기로 축소되지 않고 아래와 같은 메시지가 반환됩니다.

   모든 논리 로그 파일이 사용 중이므로 로그 파일 2(Pubs_log)을(를) 축소할 수 없습니다.
   DbId  FileId  CurrentSize  MinimumSize UsedPages     EstimatedPages 
   ----- ------- ------------ ----------- ------------- ------------------ 
   6     2       3048         128         3048          128  <- 여기 있는 모든 값은 변할 수 있습니다.
    
   (1개 행 적용됨)
    
   DBCC 실행이 완료되었습니다. DBCC에서 오류 메시지를 출력하면 시스템 관리자에게 문의하십시오.
   

3. BACKUP LOG pubs WITH TRUNCATE_ONLY를 실행합니다.
4. DBCC SHRINKFILE(pubs_log,2)을 실행합니다.
5. 이제 트랜잭션 로그가 대상 크기로 줄어듭니다.

자세한 내용은 SQL Server 2000 Books Online에서 "Shrinking the Transaction Log" 항목과 "DBCC SHRINKFILE" 항목을 참조하십시오.


SQL Server 7.0 트랜잭션 로그를 줄이는 방법

• Microsoft SQL Server 7.0에서 SHRINKFILE 및 SHRINKDATABASE 명령은 줄이려는 목표 크기를 설정합니다. 각 로그 파일은 이들 명령에 의해 표시되지만, 실제로 파일을 줄이기 위해 로그 백업이나 로그 자르기를 시도하지는 않습니다. 따라서 SHRINKFILE 또는 SHRINKDATABASE 명령을 사용한 후에는 로그 자르기 명령을 통해 파일을 줄이기 전에 로그를 자르는 명령을 실행해야 합니다.
• 아래의 기준에서 허용하는 크기보다 작은 크기로 로그를 줄일 수 없습니다.
  
  
• 원래 크기보다 로그를 작게 줄이려면 개별 파일을 DBCC SHRINKFILE을 사용하여 줄여야 합니다. DBCC SHRINKDATABASE를 사용하면 로그를 원래 크기나 명시적으로 정의한 크기보다 작게 줄일 수 없습니다. CREATE DATABASE에 모든 명시적 ALTER DATABASE 명령이 더해지므로 원래 크기는 로그의 크기로 정의됩니다. 로그의 자동 증가는 원래 크기에 포함되지 않습니다.
  
  
• 실제 로그 파일은 해당 로그 파일 내에서 현재 사용되고 있는 공간의 양보다 작을 수 없습니다. DBCC SQLPERF (LOGSPACE) 명령을 사용하면 사용된 공간의 양을 모니터 할 수 있습니다.
  
  
• Model 데이터베이스 로그의 현재 크기는 해당 서버에 있는 모든 데이터베이스 로그의 최소 크기입니다. 기본적으로 Model 데이터베이스의 로그는 1MB보다 작습니다.
  
  
• 로그를 가상 로그 파일(VLF) 경계까지만 줄일 수 있으므로 공간을 사용하고 있지 않은 경우에도 로그 파일을 VLF보다 작은 크기로 줄이는 것은 불가능합니다. 마찬가지로 VLF의 일부를 사용 중인 경우 해당 VLF에서 사용 중인 공간은 줄일 수 없습니다. 자세한 내용은 SQL Server Books Online의 "Virtual Log Files" 및 "Transaction Log Physical Architecture" 항목을 참조하십시오
  
  
• 트랜잭션 로그는 "랩어라운드" 로그입니다. 이는 특정 시간에 로그 시작 부분 및/또는 끝 부분에 "여유" 또는 "재사용 가능" 공간이 있는 VLF가 있을 수 있음을 의미합니다. 로그를 줄이려면 해당 로그의 여러 곳에 여유 공간이 있어야 하는 것이 아니라 해당 로그의 끝 부분에 "여유" 공간이 있어야 합니다. 또한, 전체 VLF를 줄일 수만 있습니다. 트랜잭션 로그를 줄이려면 로그 파일의 끝에 있는 VLF가 비활성화되어 잘려야 합니다. 자세한 내용은 SQL Server Books Online의 "Truncating the Transaction Log" 항목을 참조하십시오.

다음 몇 가지 사항에 유의하십시오.

• 시스템에 영향을 미치는 변경 작업을 수행하기 전이나 후에 항상 시스템 데이터베이스 및 사용자 데이터베이스 백업을 수행하십시오. DBCC SHRINKFILE 및 DBCC SHRINKDATABASE는 로깅되는 작업이 아니며, 이들을 실행하면 향후 트랜잭션 로그 백업도 무효화됩니다. DBCC SHRINKFILE 명령이나 DBCC SHRINKDATABASE 명령 중 하나를 실행한 후에는 반드시 전체 데이터베이스 백업을 수행해야 합니다.
  
  
• 축소가 진행될 시간에 예약된 백업이 없는지 확인하십시오.
  
  
• 오래되거나, 장기간 실행하거나 또는 복제되지 않은 트랜잭션이 없는지 확인하십시오. 이렇게 확인하려면 다음과 유사한 코드를 사용하십시오.

  DBCC OPENTRAN (database_name)

• DBCC SHRINKFILE 명령이나 DBCC SHRINKDATABASE 명령을 실행하여 축소 지점을 표시하십시오. DBCC SHRINKFILE 및 DBCC SHRINKDATABASE 사용 권한은 sysadmin 고정 서버 역할이나 db_owner 고정 데이터베이스 역할의 멤버에 기본적으로 제공되며, 권한 전가는 불가능합니다. 이들 명령의 차이점에 대한 자세한 내용은 SQL Books Online의 다음 항목을 참조하십시오. 매개 변수가 다름에 유의하십시오.
  
  

  DBCC SHRINKFILE     (file_name, target_size)
  DBCC SHRINKDATABASE (database_name, target_percent)

• 더미(dummy) 트랜잭션을 몇 개 만들어 로그를 겹치게 만든 후 BACKUP 명령을 실행하여 로그를 자르십시오. BACKUP 문은 실제로 표시된 목표 크기로 로그를 줄이고자 시도합니다.
  
  다음은 줄일 수 있도록 단일 논리 로그 파일에 대해 로그를 겹치고 로그가 잘리게 하는 더미 트랜잭션을 만드는 방법의 샘플입니다. 필요하면 사용자 환경에 맞게 샘플을 수정하십시오.

  SET NOCOUNT ON
  DECLARE @LogicalFileName sysname,
          @MaxMinutes INT,
          @NewSize INT
   
  -- *** MAKE SURE TO CHANGE THE NEXT 3 LINES WITH YOUR CRITERIA. ***
  USE     Your_Database_Name              -- This is the name of the database 
  for which the log will be shrunk.
  SELECT  @LogicalFileName = 'Your_log',  -- Use sp_helpfile to identify the logical file name that you want to shrink.
          @MaxMinutes = 10,               -- Limit on time allowed to wrap log.
          @NewSize = 100                  -- in MB
   
  -- Setup / initialize
  DECLARE @OriginalSize int
  SELECT @OriginalSize = size -- in 8K pages
    FROM sysfiles
    WHERE name = @LogicalFileName
  SELECT 'Original Size of ' + db_name() + ' LOG is ' + 
          CONVERT(VARCHAR(30),@OriginalSize) + ' 8K pages or ' + 
          CONVERT(VARCHAR(30),(@OriginalSize*8/1024)) + 'MB'
    FROM sysfiles
    WHERE name = @LogicalFileName
  CREATE TABLE DummyTrans
    (DummyColumn char (8000) not null)
   
   
  -- Wrap log and truncate it.
  DECLARE @Counter   INT,
          @StartTime DATETIME,
          @TruncLog  VARCHAR(255)
  SELECT  @StartTime = GETDATE(),
          @TruncLog = 'BACKUP LOG ' + db_name() + ' WITH TRUNCATE_ONLY'
  -- Try an initial shrink.
  DBCC SHRINKFILE (@LogicalFileName, @NewSize)
  EXEC (@TruncLog)
  -- Wrap the log if necessary.
  WHILE     @MaxMinutes > DATEDIFF (mi, @StartTime, GETDATE()) -- time has not expired
        AND @OriginalSize = (SELECT size FROM sysfiles WHERE name = @LogicalFileName)  -- the log has not shrunk    
        AND (@OriginalSize * 8 /1024) > @NewSize  -- The value passed in for new size is smaller than the current size.
    BEGIN -- Outer loop.
      SELECT @Counter = 0
      WHILE  ((@Counter < @OriginalSize / 16) AND (@Counter < 50000))
        BEGIN -- update
          INSERT DummyTrans VALUES ('Fill Log')  -- Because it is a char field it inserts 8000 bytes.
          DELETE DummyTrans
          SELECT @Counter = @Counter + 1
        END   -- update
      EXEC (@TruncLog)  -- See if a trunc of the log shrinks it.
    END   -- outer loop
  SELECT 'Final Size of ' + db_name() + ' LOG is ' +
          CONVERT(VARCHAR(30),size) + ' 8K pages or ' + 
          CONVERT(VARCHAR(30),(size*8/1024)) + 'MB'
    FROM sysfiles 
    WHERE name = @LogicalFileName
  DROP TABLE DummyTrans
  PRINT '*** Perform a full database backup ***'
  SET NOCOUNT OFF

  로그가 원래 크기에서 줄여졌는지 확인하십시오.필요한 경우 앞의 단계를 반복하십시오. 로그가 줄여지지 않을 경우 본 문서의 시작 부분에 나와 있는 요약 정보를 점검하여 로그를 줄이는 데 문제가 있는지 확인하십시오.

로그를 줄였으면 다음을 수행하십시오.

1. 마스터 데이터베이스를 전체 데이터베이스 백업합니다.
2. 사용자 데이터베이스를 전체 데이터베이스 백업합니다. SHRINK 명령이 로깅되지 않고, 전체 데이터베이스 백업을 완료하지 않으면 향후 트랜잭션 로그 백업이 무효화되기 때문에 이러한 작업이 필요합니다.

로그가 커지는 이유를 확인하려면 열린 트랜잭션, 장기간 실행되는 트랜잭션, 복제되지 않은 트랜잭션 또는 많은 양의 데이터를 사용하는 트랜잭션을 점검하면 됩니다.


데이터베이스 파일명 찾기

sp_helpfile
GO

위의 쿼리를 실행하면 트랜잭션 로그명을 알수 있습니다.


원문:
http://support.microsoft.com/kb/907511/ko
http://support.microsoft.com/kb/272318/
http://support.microsoft.com/kb/256650/KO/