문제 상황
크기가 2TB를 넘는 TestDB 데이터베이스에서 데이터베이스 무결성 검사 작업이 IOPS(초당 입출력 연산) 문제로 인해 계속 실패하고 있었습니다. 단일 데이터 파일의 용량이 너무 커서 관리 자체가 어려운 상황이었고, 이를 해결하기 위해 데이터를 두 개의 데이터 파일로 분할하기로 결정했습니다.
현재 드라이브 공간과 데이터 파일의 상태는 다음과 같습니다. 기존 데이터 파일은 N:\ 드라이브에 위치해 있으며, 같은 위치에 새로운 파일을 추가로 생성할 계획입니다.
여기서 사용할 접근 방식은 EMPTYFILE 명령으로 데이터 전송을 시작한 뒤, 원하는 시점에 쿼리를 수동으로 중단하여 데이터 이동을 강제로 멈추는 것입니다. 참고로 쿼리를 중간에 수동으로 중지하더라도 데이터베이스의 무결성이나 일관성에는 아무런 영향이 없으므로 안심해도 됩니다. 이후 mdf 파일을 축소(shrink)하여 확보된 여유 공간을 회수하면 됩니다.
해결 방법
아래 단계를 따르면 SQL Server의 데이터를 여러 데이터 파일로 분할할 수 있습니다.
1단계: 보조 데이터 파일(NDF) 추가
먼저 데이터를 옮겨 담을 보조 데이터 파일을 추가해야 합니다. 이 파일은 NDF(Next Data File) 형식으로 추가되며, 아래 스크립트를 실행하면 TestDB 데이터베이스에 새 데이터 파일이 생성됩니다.
USE [master]
GO
ALTER DATABASE [TestDB] ADD FILE ( NAME = N'TestDB_1', FILENAME =
N'N:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\TestDB_1.ndf' ,
SIZE = 209715200KB , FILEGROWTH = 5242880KB ) TO FILEGROUP [PRIMARY]
GO 이 스크립트를 실행하면 N:\ 드라이브에 TestDB_1이라는 이름의 새 데이터 파일이 200GB 크기로 생성됩니다(여기서는 해당 데이터베이스 환경에 맞게 설정했습니다). 파일 증가 크기는 5GB로 지정했으며, 새 파일은 PRIMARY 파일 그룹에 추가됩니다.
2단계: DBCC EMPTYFILE 실행
데이터 파일 추가가 완료되면 TestDB 데이터베이스에서 DBCC EMPTYFILE 작업을 시작합니다. 기본 구문은 다음과 같습니다.
use YOURDATABASE
go
dbcc shrinkfile('mdfFileName',emptyfile)이번 사례에 적용하면 다음과 같습니다.
USE [TestDB]
go
DBCC shrinkfile ('TestDB',emptyfile)여기서 'TestDB'는 데이터를 제거하려는 대상 파일, 즉 mdf 파일의 논리적 이름입니다.
3단계: 데이터 이동 진행 상황 모니터링
EMPTYFILE 작업이 시작되면 mdf에서 ndf로 얼마나 많은 데이터가 이동했는지 지속적으로 확인해야 합니다. 아래 쿼리를 사용하면 각 파일의 크기, 사용 공간, 여유 공간 등을 한눈에 파악할 수 있습니다.
USE [TestDB]
GO
SELECT
[TYPE] = A.TYPE_DESC
,[FILE_Name] = A.name
,[FILEGROUP_NAME] = fg.name
,[File_Location] = A.PHYSICAL_NAME
,[FILESIZE_MB] = CONVERT(DECIMAL(10,2),A.SIZE/128.0)
,[USEDSPACE_MB] = CONVERT(DECIMAL(10,2),A.SIZE/128.0 - ((SIZE/128.0) - CAST(FILEPROPERTY(A.NAME, 'SPACEUSED') AS INT)/128.0))
,[USEDSPACE_%] = CAST((CAST(FILEPROPERTY(A.name, 'SpaceUsed')/128.0 AS DECIMAL(10,2))/CAST(A.size/128.0 AS DECIMAL(10,2)))*100 AS DECIMAL(10,2))
,[FREESPACE_MB] = CONVERT(DECIMAL(10,2),A.SIZE/128.0 - CAST(FILEPROPERTY(A.NAME, 'SPACEUSED') AS INT)/128.0)
,[FREESPACE_%] = CONVERT(DECIMAL(10,2),((A.SIZE/128.0 - CAST(FILEPROPERTY(A.NAME, 'SPACEUSED') AS INT)/128.0)/(A.SIZE/128.0))*100)
,[AutoGrow] = 'By ' + CASE is_percent_growth WHEN 0 THEN CAST(growth/128 AS VARCHAR(10)) + ' MB -'
WHEN 1 THEN CAST(growth AS VARCHAR(10)) + '% -' ELSE '' END
+ CASE max_size WHEN 0 THEN 'DISABLED' WHEN -1 THEN ' Unrestricted'
ELSE ' Restricted to ' + CAST(max_size/(128*1024) AS VARCHAR(10)) + ' GB' END
+ CASE is_percent_growth WHEN 1 THEN ' [autogrowth by percent, BAD setting!]' ELSE '' END
FROM sys.database_files A LEFT JOIN sys.filegroups fg ON A.data_space_id = fg.data_space_id
order by A.TYPE desc, A.NAME;이번 작업에서는 ndf 파일이 약 500GB 정도가 되기를 원했기 때문에, 모니터링 쿼리로 ndf 크기가 목표치에 도달하면 EMPTYFILE 작업을 중단하면 됩니다.
4단계: mdf 파일 공간 회수(SHRINK)
EMPTYFILE 작업을 중지한 후에는 아래 쿼리를 사용해 mdf 파일의 여유 공간을 수동으로 회수해야 합니다.
DBCC Shrinkfile('TestDB', 1500000) -- 주의할 점은 축소 작업을 한 번에 크게 실행하지 말고, 작은 단위로 나누어 여러 번 수행하는 것이 좋다는 것입니다.
이번 사례에서는 mdf가 2TB였고 500GB를 ndf로 이동했으므로, mdf에서 500GB를 회수할 수 있었습니다. 위 쿼리로 바로 그 공간을 회수한 것입니다.
이 과정은 필요에 따라 여러 번 반복할 수 있습니다. 저장 공간 상황에 맞춰 데이터 이동 작업을 중간에 수동으로 중지하고, 다시 공간을 회수하는 방식으로 진행하면 됩니다.
주의사항: file_id 1인 기본 데이터 파일은 완전히 비울 수 없음
mdf 파일에 EMPTYFILE을 사용할 때 한 가지 알아두어야 할 점이 있습니다. 파일 ID가 1인 기본(primary) 데이터 파일의 내용은 끝까지 완전히 비울 수 없습니다. 파일 ID 번호를 확인하려면 아래 스크립트를 실행하세요.
select file_id, name,physical_name from sys.database_files예를 들어 파일명이 "mo"이고 file_id가 1인 경우, 이 파일을 비우려고 하면 오류 메시지가 발생합니다.
그 이유는 원본 파일 내부에 시스템 정보가 포함되어 있어 이를 제거할 수 없기 때문입니다. 반면, 동일한 명령을 "mo2data" 같은 다른 데이터 파일에 실행하면 EMPTYFILE 작업이 정상적으로 성공합니다.
마무리
데이터 이동 작업이 모두 완료되면 반드시 아래의 데이터베이스 유지 관리 작업을 실행해야 합니다.
- 인덱스 최적화 작업(Index Optimize Job)
- 무결성 검사 작업(Integrity Check Job)
- 전체 데이터베이스 백업 작업(Full Database Backup Job)
내용에 대해 궁금한 점이나 의견이 있다면 피드백 탭을 통해 알려주세요. 언제든지 함께 이야기 나누겠습니다.