MsSQL Shrink

DataBase생성시 .mdf .ndf .ldf 파일이 생성된다. (3개가 1쌍이며 여러 쌍을 가질 수 있다)

 

여기서 DB를 지울 경우 실제 메인인 .mdf 크기가 줄어들기를 기대하지만, 어림없지

별도로 Shrink 작업을 해줘야 파편화된 디스크를 모아주기때문에 크기가 줄어드는 것을 확인할 수 있다.

 

DB파일크기와 테이블크기 확인하는 쿼리는 아래와 같다.

 SELECT 
 b.groupname AS 'File Group'
 , Name
 , [Filename]
 , CONVERT (Decimal(15,2),ROUND(a.Size/128.000,2))  [Currently Allocated Space (MB)]
 , CONVERT (Decimal(15,2)
 , ROUND(FILEPROPERTY(a.Name,'SpaceUsed')/128.000,2)) AS [Space Used (MB)]
 , CONVERT (Decimal(15,2)
 , ROUND((a.Size-FILEPROPERTY(a.Name,'SpaceUsed'))/128.000,2)) AS [Available Space (MB)] 
FROM dbo.sysfiles a (NOLOCK) 
JOIN sysfilegroups b (NOLOCK) ON a.groupid = b.groupid 
ORDER BY b.groupname

SELECT CONVERT(VARCHAR(30), MIN(o.name)) AS t_name
     , LTRIM(STR(SUM(reserved) * 8192.0 / 1024.0, 15, 0) + ' KB') AS t_size
FROM   sysindexes i
           INNER JOIN sysobjects o ON o.id = i.id
WHERE  i.indid IN (0, 1, 255)
   AND o.xtype = 'U'
GROUP BY
       i.id
ORDER BY
       SUM(reserved) * 8192.0 / 1024.0 DESC

 

Shrink 쿼리로 해당 테이블을 파편모으기 할 수 있다. (다른 구문으로 파일별로 shrink 가능)

 

다만, 이후엔 인덱스 단편화를 초래한다.

따라서 이후에 ALTER INDEX REORGANIZE 및 ALTER INDEX REBUILD이 필요하다.

 

조각화(avg_fragmentation_in_percent)를 확인하여

SELECT a.object_id, object_name(a.object_id) AS TableName,
    a.index_id, name AS IndedxName, avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats
    (DB_ID (N'AdventureWorks2016_EXT')
        , OBJECT_ID(N'HumanResources.Employee')
        , NULL
        , NULL
        , NULL) AS a
INNER JOIN sys.indexes AS b
    ON a.object_id = b.object_id
    AND a.index_id = b.index_id;

테이블의 모든 인덱스에 대해
5~30 -> REORGANIZE

ALTER INDEX ALL ON HumanResources.Employee
   REORGANIZE;

30 ~ -> REBUILD 

ALTER INDEX ALL ON Production.Product
REBUILD WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = ON,
              STATISTICS_NORECOMPUTE = ON)
;

 

참고
docs.microsoft.com/ko-kr/sql/relational-databases/indexes/reorganize-and-rebuild-indexes?view=sql-server-ver15moonistar.tistory.com/157
burning-dba.tistory.com/41