mdf 파일에서 동일한 파일 그룹의 ndf 파일로 데이터 마이그레이션

문제: 크기가 2TB를 초과하는 TestDB 데이터베이스에 대한 IOPS 문제로 인해 데이터베이스 무결성 작업이 실패했습니다. 파일 크기가 커서 데이터베이스 관리가 어려워지고 있습니다.

접근 방식: 이 문제를 해결하기 위해 데이터를 2개의 데이터 파일로 분할하기로 결정했습니다. 따라서 드라이브 공간 및 데이터 파일의 현재 상태는 다음과 같습니다.

해결책 :아래 단계에 따라 여러 SQL Server 데이터 파일 간에 데이터를 분할합니다. 먼저 데이터를 삽입할 보조 데이터 파일을 추가해야 합니다. ndf(다음 데이터 파일)로 추가됩니다. 아래 스크립트를 실행하여 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로 설정했으며 데이터 파일이 기본 파일 그룹에 추가됩니다.

이제 데이터 파일을 추가한 후 TestDB 데이터베이스에서 DBCC emptyfile 작업을 시작합니다. 구문은 다음과 같습니다.

use YOURDATABASE
go
dbcc shrinkfile(‘mdfFileName’,emptyfile)

따라서 우리의 경우는 다음과 같습니다.

USE [TestDB]

go

DBCC shrinkfile ('TestDB’,emptyfile)

여기서 TestDB는 데이터, 즉 mdf 파일을 제거하려는 파일의 논리적 이름입니다. 이제 이 작업을 시작했으면 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 작업을 중지할 수 있습니다. emptyfile 작업이 중지되면 아래 쿼리를 사용하여 mdf의 여유 공간을 수동으로 회수해야 합니다. :

DBCC Shrinkfile('TestDB', 1500000) --  

더 작은 청크로 크기를 변경해야 합니다.

이제 mdf는 2TB였고 500GB를 ndf로 옮겼습니다. 따라서 위 쿼리를 사용하여 방금 회수한 mdf에서 500GB를 회수할 수 있습니다.

이 단계를 여러 번 반복하여 데이터 파일 간에 데이터를 이동하고 스토리지에 따라 수동으로 작업을 중지한 다음 공간을 다시 확보할 수 있습니다.

mdf에서 emptyfile을 사용할 때 주의할 점은 파일 ID가 1인 기본 데이터 파일의 내용을 완전히 비울 수 없다는 것입니다. 파일 ID 번호를 얻으려면 이 스크립트를 실행하십시오.

select file_id, name,physical_name from sys.database_files

여기 이 예에서 파일 이름은 "mo"이고 file_id는 1입니다. file_id가 1인 mo 파일을 비우려고 하면 이 오류 메시지가 표시됩니다.

원본 파일에 비울 수 없는 시스템 정보가 있기 때문입니다. 하지만 다른 데이터 파일 "mo2data"에 동일한 명령을 시도하면 빈 파일 명령이 성공합니다.

결론

이 데이터 이동 작업이 완료되면 데이터베이스 유지 관리 작업을 실행하십시오.– 인덱스 최적화 작업 – 무결성 검사 작업 – 전체 데이터베이스 백업 작업.

피드백 탭을 사용하여 의견을 작성하거나 질문하십시오. 저희와 대화를 시작할 수도 있습니다.