데이터베이스 mdf 파일 축소
카테고리 없음 / 2024. 2. 2. 12:02
개념 이해를 위해 mdf, ndf 파일은 OS에서 받은 데이터베이스 공간이다. 이 공간은 여러 테이블이나 인덱스 등이 사용한다. 테이블이 어떤 이유로 매우 커졌다가 내부 데이터를 delete로 지우면 할당을 해지한다. 이 때 해지한 공간은 완전히 할당 해지 되는 경우가 별로 없고 그 공간은 해당 테이블이나 인덱스에서만 재활용 가능하다. 이를 다른 테이블이나 인덱스에서도 사용 가능하게 하려면 heap rebuild, clustered index rebuild, index rebuild 등을 해 완전히 할당 해지 해주어야 한다. 또 다른 방법은 reorganize 하는 방법이 있다. 이렇게 다른 테이블이나 인덱스도 사용할 수 있는 공간이 되면 비로서 mdf 파일의 크기를 줄여 OS로 공간을 반환할 수도 있다. reorganize 는 하드 디스크 조각 모음으로 이해하면 비슷하다. rebuild 는 순차 읽기 성능이 좋고 reorganize 는 순차 읽기 성능에 좋지 않다. 그런데, 요즘 대부분 SSD를 쓰니 논리 순서와 할당 순서의 일관성이 성능에 영향을 끼치지 않는다.
아래 결과에서 unallocated space 와 unused 사이즈에 주목한다.
sp_spaceused
database_name database_size unallocated space
------------------------------ ------------------ ------------------
SqlLogManDmvRepositoryCafeV3 5354120.00 MB 66097.92 MB
reserved data index_size unused
------------------ ------------------ ------------------ ------------------
5411846224 KB 549654168 KB 178159848 KB 4684032208 KB
Completion time: 2024-01-08T15:50:42.0522621+09:00
--> 인덱스 리빌드 스크립트 수행 후 (6시간)
-- 인덱스 리빌드는 unused 사이즈를 unallocated space 로 반환하게 된다.
sp_spaceused
database_name database_size unallocated space
------------------------------ ------------------ ------------------
SqlLogManDmvRepositoryCafeV3 5440840.00 MB 4692463.71 MB
reserved data index_size unused
------------------ ------------------ ------------------ ------------------
760496424 KB 574814688 KB 184509664 KB 1172072 KB
줄이기 늘이기
-- 조회
select * from sysaltfiles where dbid = db_id()
-- TargetSize 는 무시된다. 사이즈는 MB 단위
-- 2022 에는 WAIT_AT_LOW_PRIORITY 로 장기 수행 Sch-S의 영향을 줄일수 있다.
DBCC SHRINKFILE ('SqlLogManDmvRepositoryCafeV3', 0, TRUNCATEONLY);
-- 늘이는 명령어
ALTER DATABASE SqlLogManDmvRepositoryCafeV3 MODIFY FILE ( NAME = 'SqlLogManDmvRepositoryCafeV3', SIZE = 1TB )
reorganize 방법 (수정 좀 해서 써야 할 듯)
DECLARE @i int, @sql varchar(1000)
DECLARE @tablename varchar(1000),@ownerName varchar(1000)
SET @i = 1
DECLARE DB_Cursor CURSOR FOR
SELECT TABLE_SCHEMA, TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
--and TABLE_NAME not in (
-- select distinct object_name(object_id) --, name, type_desc
-- from sys.indexes
-- where
-- allow_row_locks = 0
-- or allow_page_locks = 0)
ORDER BY TABLE_SCHEMA, TABLE_NAME
OPEN DB_Cursor
FETCH NEXT FROM DB_Cursor
INTO @ownerName, @tablename
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = 'ALTER INDEX ALL ON ' + @ownerName + '.' + @tablename + ' REORGANIZE '
EXEC (@sql)
PRINT CONVERT(VARCHAR, @i) + '__' + @ownerName + '.' + @tablename + '............ OK'
SET @i = @i + 1
FETCH NEXT FROM DB_Cursor
INTO @ownerName, @tablename
END
CLOSE DB_Cursor
DEALLOCATE DB_Cursor
온라인에 사용 불가능하다. 엔터프라이즈는 온라인 옵션 가능 (수정 좀 해서 써야 할 듯)
DECLARE @i int, @sql varchar(1000)
DECLARE @tablename varchar(1000),@ownerName varchar(1000)
SET @i = 1
DECLARE DB_Cursor CURSOR FOR
SELECT TABLE_SCHEMA, TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' ORDER BY TABLE_SCHEMA, TABLE_NAME
OPEN DB_Cursor
FETCH NEXT FROM DB_Cursor
INTO @ownerName, @tablename
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = 'ALTER INDEX ALL ON ' + @ownerName + '.' + @tablename + ' REBUILD WITH (PAD_INDEX = ON, FILLFACTOR = 90, ONLINE = ON)
EXEC (@sql)
PRINT CONVERT(VARCHAR, @i) + '__' + @ownerName + '.' + @tablename + '............ OK'
SET @i = @i + 1
FETCH NEXT FROM DB_Cursor
INTO @ownerName, @tablename
END
CLOSE DB_Cursor
DEALLOCATE DB_Cursor