【SQL Server】同じファイルグループ内でmdfファイルからndfファイルへデータを移行する方法

課題

サイズが2TBを超えるTestDBデータベースにおいて、IOPS(ディスクI/O)の問題により、データベース整合性チェックジョブが失敗していました。ファイルサイズが大きすぎるため、データベース自体の管理も難しくなっていました。

アプローチ

この問題を解決するため、データを2つのデータファイルに分割することにしました。現在のドライブ容量とデータファイルの状態は以下のとおりです。

データファイルは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:移行状況の監視

操作を開始したら、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;

ステップ4:移行の停止と空き領域の回収

今回の方針では、ndfをおよそ500GBにすることを目標としたため、ndfがこのサイズに達した時点でEMPTYFILE操作を停止できます。操作を停止した後、以下のクエリでmdfの空き領域を手動で回収します。

DBCC Shrinkfile('TestDB', 1500000) --  

圧縮時のサイズ指定は、小さな単位に分けて段階的に実行することをおすすめします。

今回、mdfは2TBあり、うち500GBをndfへ移動したため、mdfからは500GBを回収できます。上記のクエリでその領域を実際に回収しました。

この手順は複数回繰り返すことが可能です。ストレージの状況に応じて途中で操作を手動停止しながらデータを移動し、その都度空き領域を回収していきましょう。

注意点:ファイルID 1のプライマリデータファイルは完全には空にできない

mdfに対してEMPTYFILEを使用する際の重要な注意点として、ファイルID 1のプライマリデータファイルの中身を完全に空にすることはできません。ファイルIDを確認するには、以下のスクリプトを実行します。

select file_id, name,physical_name from sys.database_files

この例では、ファイル名は「mo」、file_idは1です。file_id 1のファイル「mo」を空にしようとすると、エラーメッセージが表示されます。

これは、元のファイル内にはシステム情報が含まれており、その部分は空にできないためです。一方、別のデータファイル「mo2data」に対して同じコマンドを実行すれば、EMPTYFILEコマンドは正常に完了します。

まとめ

データ移行作業が完了したら、必ず以下のデータベースメンテナンスジョブを実行してください。

• インデックス最適化ジョブ
• 整合性チェックジョブ
• データベース全体バックアップジョブ

ご意見やご質問がある場合は、フィードバックタブをご利用ください。私たちとの対話を開始することもできます。