同じファイルグループ内のmdfファイルからndfファイルにデータを移行する

問題： サイズが2TBを超えるTestDBデータベースのIOPSの問題が原因で、データベース整合性ジョブが失敗していました。ファイルサイズが大きいため、データベースの管理が難しくなっています。

アプローチ： これをトラブルシューティングするために、データを2つのデータファイルに分割することにしました。したがって、ドライブスペースとデータファイルの現在の状態は次のとおりです。

データファイルはN：\ドライブでホストされており、同じ場所に別のファイルを作成します。私たちのアプローチは、emptyfileコマンドを使用してデータ転送を開始し、その間に手動でクエリを停止して、データの移動を強制的に停止することです。間にクエリを手動で停止しても、データベース（整合性/整合性）には影響しないことに注意してください。次に、mdfファイルを縮小して空き領域を再利用します。

解決策 ：以下の手順に従って、複数の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という名前の新しいデータファイルが追加され、サイズは200 GBになります（これは、データベースのコンテキストで取得しました）。ファイルの容量を5GBに設定し、データファイルをプライマリファイルグループに追加します。

ここで、データファイルを追加した後、TestDBデータベースでDBCCemptyfile操作を開始します。構文は次のようになります：

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を約500 GBにする必要があったため、ndfがこのサイズに達したら、emptyfile操作を停止できます。emptyfile操作を停止したら、以下のクエリを使用してmdfの空き領域を手動で再利用する必要があります。 ：

DBCC Shrinkfile('TestDB', 1500000) --  

サイズを小さなチャンクに変更する必要があります

mdfが2TBになったので、500 GBをndfに移動しました。したがって、500 GBはmdfから再利用可能であり、上記のクエリを使用して再利用できます。

この手順を複数回繰り返して、データファイル間でデータを移動し、ストレージに応じてその間の操作を手動で停止してから、スペースを再利用できます。

mdfでemptyfileを使用しているときに、ファイルID 1のプライマリデータファイルの内容を完全に空にすることはできないことに注意してください。ファイルID番号を取得するには、このスクリプトを実行します。

select file_id, name,physical_name from sys.database_files

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

これは、元のファイル内にシステム情報があり、空にできないためです。ただし、他のデータファイル「mo2data」で同じコマンドを実行すると、空のファイルコマンドは成功します。

結論

このデータ移動アクティビティが完了したら、データベース保守ジョブを実行してください。–インデックス最適化ジョブ–整合性チェックジョブ–完全なデータベースバックアップジョブ。

コメントや質問をするには、[フィードバック]タブを使用します。私たちと会話を始めることもできます。