DBCC SHRINKFILE & Internal

사용 Script

1. DBCC SHRINKFILE Automation Script

• 대상 장비 변경 시 아래 변수만 수정하시면 됩니다.

• @TargetSizeMB : SHRINKFILE 작업 후 데이터 파일 크기
• @ShrinkUnitSize

• SHRINKFILE 수행 단위(MB), 값이 클 경우 서비스에 영향을 줄 수 있으며, 일반적으로 1GB 혹은 사용량이 적을 경우 5GB까지 변경 후 사용
• 사용량이 많은 시스템일 경우 100MB/512MB로 조절 후 사용 필요, ShrinkfileSize를 줄일 경우 시스템 영향도를 줄일 수 있습니다.

• @DatabaseName : SHRINKFILE 대상 데이터베이스 이름, 가급적 해당 데이터베이스 이동 후 사용(use 데이터베이명 혹은 SSMS상 SHRINKFILE 대상 DB로 이동)

SET NOCOUNT ON use 'SHRINKFILE데이터베이스명 GO  DECLARE                    @shrinkUnitSize            INT                        -- Unit of Shrinkfile Size ,      @TotalSizeMB        INT                        -- Current DataFile Size ,      @TargetSizeMB       INT                        -- Size Of Target ,      @filename                  VARCHAR(50)         -- Shrinkfile File Name ,      @start_time                DATETIME            -- Start Time ,      @databaseName       SYSNAME                    -- Target Database Name ,      @loopCnt                   INT                        -- loop Variables   SET @TargetSizeMB = 300000  -- 현재 데이터파일 크기 SET @shrinkUnitSize = 500 -- SHRINKFILE Size 단위(MB) - 초기 500MB SET @DatabaseName = 'SHRINKFILE데이터베이스명' SET @loopCnt = 1   --SET Current Database Size, Change FileId or Set the FileName, 데이터베이스 Logical Finename SELECT @TotalSizeMB = size/128, @filename = name   FROM sys.sysaltfiles where dbid=db_id(@DatabaseName) and fileid = 1   --SHRINKFILE 결과 SELECT @TotalSizeMB AS CurrentSizeMB, GETDATE() AS StartTime   WHILE (@TotalSizeMB > @TargetSizeMB) -- for Loop Shrinkfile to @TargetSize BEGIN          SET @start_time = GETDATE()          SET @TotalSizeMB = @TotalSizeMB - @shrinkUnitSize                   DBCC SHRINKFILE (@filename, @TotalSizeMB) WITH NO_INFOMSGS            PRINT 'idx = ' + CAST(@loopCnt AS varchar(10)) +               ', totalSizeMB = ' + CAST(@TotalSizeMB AS varchar(10))               + ', duration = ' + CAST(DATEDIFF(s, @start_time, getdate()) AS varchar(10))            SET @loopCnt = @loopCnt + 1 END   --SHRINKFILE 결과 SELECT @TotalSizeMB AS ShrinkSizeMB, GETDATE() AS EndTime GO

 

 

2. DBCC SHRINKFILE internal

• DBCC SHRINKFILE은 DbccSpaceReclaim->DbccFilesCompact->DbccLOBCompact 입니다.
• DbccSpaceReclaim : DML구문으로 인해 발생 된 미사용 Extents에 대한 Clean-up과 데이터 이동에 사용 된 빈 Extents에 대한 제거 작업
• DbccFilesCompact : Truncate 대상 이전에 있는 페이지를 이동하고 Truncation 수행

• DBCC SHRINKFILE은 파일의 끝 지점부터 수행 순차적으로 수행 됩니다. 끝 지정에 위치한 Extents들을 데이터 파일 내 비어 있는 공간으로 이동을 하게 되고,

   이 단계에서 인덱스 Page들에 대한 Linked List Chain이 느슨하게 풀리게 되면서 인덱스 조각도가 심화되는 상황이 발생 됩니다.

• DbccLOBCompact : LOB(Large Object)에 대한 Compact 작업 수행

è 데이터 파일에 대한 DBCC SHRINKFILE 성능 저하 발생 구간

Step	Command	Description
1	DbccSpaceReclaim	Clean up deferred allocations and purge empty extents preparing for data moves.
2	DbccFilesCompact	Moves pages beyond the target to before the target and truncate file as required.
3	DbccLOBCompact	Compacting the LOB data.

 

è 1GB짜리 데이터 파일에 대한 SHRINKFILE 진행 예

1. 미사용 Extents에 대한 Clean-up과 데이터 이동에 사용 된 빈 Extents에 대한 제거 작업이 진행 되고, 1GB 중 데이터파일의 끝 부분의 여유 공간을 확인합니다. -> DbccSpaceReclaim
2. 750MB 이내에 할당되지 않은 Page에 대한 정보를 수집합니다.
3. 750MB->1GB 사이에 위치한 GAM(Global Allocation Map)에 Marking되어 있는 모든 페이지를 검색 한 후, Truncation이 수행되어야 하는 위치(750MB->1GB=250MB)에 할당되어 있는 Page(데이터 혹은 인덱스)들을 ② 에서 조회한

할당되지 않은 Page로 이동합니다. -> DbccFilesCompact

4. DBCC SHRINKFILE 구문에 TRUNCATEONLY 구문이 명시되어 있지 않은 경우, LOB(Large Object)에 대한 Compact작업이 수행되게 됩니다. -> DbccLOBCompact
5. DBCC SHRINKFILE 옵션

• TRUNCATEONLY : 파일 끝의 모든 여유 공간을 운영 체제로 해제하지만 파일 내에서 페이지 이동을 수행하지 않습니다. 데이터 파일은 마지막으로 할당된 익스텐트까지만 축소됩니다
• NOTRUNCATEONLY : target_percent를 지정하거나 지정하지 않고 데이터 파일의 끝에 있는 할당된 페이지를 파일 앞의 할당되지 않은 페이지로 이동합니다.  파일 끝의 여유 공간이 운영 체제에 반환되지 않고, 파일의 물리적 크기가 변경되지 않습니다. 그러므로 NOTRUNCATE를 지정하면 파일이 축소되지 않는 것처럼 보입니다.