SQL Serverのデータベースファイルの移動について

同僚から、SQL Serverがサーバーのディスク容量を圧迫しているとの相談があった。

まずはどんなものかと確認したところ、

こいつはマズいという事で、これを解消する対応をしたので、その手順の覚書。

上記の画像でもそうだが、SQL Serverを運用していると、データベースのMDFファイル（データファイル）が肥大化し、ディスク容量を圧迫することがある。
今回はこれを解消するために、MDFファイルを空き容量のある別のドライブに移動する対応を行った。

本記事の対象環境・バージョンについて

本記事で使用している環境:

SQL Server Management Studio (SSMS) バージョン: 18.11.1
基本的にSQL Server 2016以降を対象とした手順

バージョン・エディション依存の注意事項:

画面表示や一部の手順は、SQL Serverのバージョンやエディションによって異なる場合がある
特に古いバージョン（SQL Server 2012以前）では、GUIの配置や表示が大きく異なることがある
Express エディションやWeb エディションでは、一部のGUI機能が制限されている場合がある
本記事の手順は一般的なStandard/Enterprise エディションを基準としているが、T-SQLコマンドはほぼ全てのエディションで共通して使用可能

使用しているSQL Serverのバージョンやエディションに応じて、適宜画面表示や手順を読み替えていただきたい。

重要な事前準備：作業を始める前に必ず以下を確認

どの方法を選択するにしても、以下の準備作業は必ず実施すること。データの安全性を確保し、スムーズな移行を実現するために不可欠だ。

1. データベースのフルバックアップ取得

最重要項目。 万が一の事態に備え、作業直前に必ず対象データベースのフルバックアップを取得する。

2. メンテナンス時間の確保

ファイル移動作業中は、対象データベースへのアクセスを停止する必要がある。ユーザー影響を最小限にするため、事前にメンテナンス時間を計画し、関係者に周知する。

3. 現在のファイルパスと論理名の確認

SQL Server Management Studio (SSMS) で対象のデータベースを右クリックし、「プロパティ」 > 「ファイル」ページで、現在のMDFファイルとLDFファイルの「パス」と「論理名」を控えておく。

※今回のDBはdocker上に作成しているのでパスがちょっと変。

T-SQLでも確認できる:

USE YourDatabaseName; -- 対象のデータベース名に置き換えること
SELECT
    name AS LogicalName,
    physical_name AS CurrentPath,
    type_desc -- ROWS_DATA_FILE (mdf), LOG_FILE (ldf)
FROM sys.database_files;

4. 新しい保存先ドライブの空き容量確認

移動先のドライブに、MDFファイルとLDFファイルを格納するのに十分な空き容量があることを確認する。

5. SQL Serverサービスアカウントの権限確認

新しい保存先フォルダに対して、SQL Serverのサービスアカウント（通常は NT SERVICE\MSSQLSERVER や NT SERVICE\SQLEXPRESS など）がフルコントロールの権限を持っていることを確認する。権限がない場合は、事前にフォルダのプロパティからセキュリティ設定で権限を付与する。

移動方法の選択：デタッチ・アタッチ or ALTER DATABASE

MDF/LDFファイルを移動するには、主に以下の2つの方法がある。

方法1: データベースのデタッチ・アタッチ

概要: データベースをSQL Server インスタンスから一時的に切り離し（デタッチ）、ファイルを移動後、再度接続（アタッチ）する方法
比較的シンプルで直感的な操作が可能

方法2: ALTER DATABASE MODIFY FILE を使用

概要: データベースをオフライン状態にし、ALTER DATABASE コマンドでSQL Serverに登録されているファイルパスを変更後、ファイルを物理的に移動し、再度オンラインにする方法
SQL Serverの内部的な仕組みに沿ったより制御された手順

どちらの方法でも結果は同じだが、手順やダウンタイムの特性が若干異なる。状況に合わせて選択すること。
私は今回、方法1かつSSMSを利用して作業を行った。

方法1: データベースのデタッチ・アタッチによるファイル移動

手順1: データベースのデタッチ

SSMSを使用する場合:

1.オブジェクトエクスプローラーで、移動したいデータベースを右クリック
2.「タスク」 > 「デタッチ」を選択

3.「データベースのデタッチ」ダイアログで、「接続の削除」にチェックを入れる。「統計の更新」もチェックしておくと良い

4.「OK」をクリック

T-SQLを使用する場合:

USE master;
GO
-- 既存の接続を強制的に切断し、シングルユーザーモードに移行
ALTER DATABASE YourDatabaseName SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO
-- データベースをデタッチ
EXEC sp_detach_db 'YourDatabaseName';
GO

注意: YourDatabaseName は対象のデータベース名に置き換えること。

手順2: MDFファイルとLDFファイルの移動

エクスプローラーなどを使用し、デタッチしたデータベースのMDFファイルとLDFファイル（事前準備で確認した現在のパスにあるファイル）を、新しいドライブの指定フォルダに移動する。

手順3: データベースのアタッチ

SSMSを使用する場合:

1.オブジェクトエクスプローラーで、「データベース」フォルダを右クリック
2.「アタッチ」を選択

3.「データベースのアタッチ」ダイアログで、「追加」ボタンをクリック

4.「データベースfileの検索」ダイアログで、新しいドライブに移動したMDFファイルを選択し、「OK」をクリック

5.通常、LDFファイルも自動的に検出される。下部の「データベースの詳細」ペインで、MDFファイルとLDFファイルの「現在のファイルのパス」が新しい場所になっていることを確認する。もしLDFファイルのパスが正しくない場合は、手動で修正する

6.「OK」をクリックしてアタッチ

T-SQLを使用する場合:

USE master;
GO
CREATE DATABASE YourDatabaseName
ON (FILENAME = '新しいドライブ:\新しいフォルダ\YourDatabaseName.mdf'), -- 新しいMDFファイルのフルパス
   (FILENAME = '新しいドライブ:\新しいフォルダ\YourDatabaseName_log.ldf') -- 新しいLDFファイルのフルパス
FOR ATTACH;
GO
-- 必要に応じてマルチユーザーモードに戻す
ALTER DATABASE YourDatabaseName SET MULTI_USER;
GO

注意: YourDatabaseName、MDFファイルのパス、LDFファイルのパスを実際の値に置き換えること。

方法2: ALTER DATABASE MODIFY FILE を使用したファイル移動

手順1: データベースをオフラインにする

SSMSを使用する場合:

オブジェクトエクスプローラーで、移動したいデータベースを右クリック
「タスク」 > 「オフラインにする」を選択
確認ダイアログで「接続の削除」にチェックを入れ、「OK」をクリック

T-SQLを使用する場合:

USE master;
GO
ALTER DATABASE YourDatabaseName SET OFFLINE WITH ROLLBACK IMMEDIATE; -- 既存の接続を強制的に切断してオフラインにする
GO

注意: YourDatabaseName は対象のデータベース名に置き換えること。

手順2: ファイルのパスを変更 (SQL Serverに新しいパスを登録)

このステップでは、SQL Serverのシステムカタログに記録されているファイルのパス情報を更新する。この時点ではまだ実際のファイルは移動しない。

T-SQLで以下のコマンドを実行する。NAMEには事前準備で確認した論理ファイル名、FILENAMEには新しいファイルのフルパスを指定する。

USE master;
GO

-- データファイル(mdf)のパスを変更
ALTER DATABASE YourDatabaseName
    MODIFY FILE (NAME = '論理データファイル名', FILENAME = '新しいドライブ:\新しいフォルダ\YourDatabaseName.mdf');
    -- 例: NAME = 'MyDatabase_Data', FILENAME = 'E:\SQLData\MyDatabase.mdf'
GO

-- ログファイル(ldf)のパスを変更
ALTER DATABASE YourDatabaseName
    MODIFY FILE (NAME = '論理ログファイル名', FILENAME = '新しいドライブ:\新しいフォルダ\YourDatabaseName_log.ldf');
    -- 例: NAME = 'MyDatabase_Log', FILENAME = 'E:\SQLLogs\MyDatabase_log.ldf'
GO

注意: - YourDatabaseName を実際のデータベース名に置き換える - 論理データファイル名 と 論理ログファイル名 を、事前準備で確認した実際の論理名に置き換える - MDFとLDFの新しいフルパスを正確に指定する

手順3: MDFファイルとLDFファイルの物理的な移動

エクスプローラーなどを使用し、実際にMDFファイルとLDFファイルを古い場所から、手順2で FILENAME に指定した新しいドライブのフォルダに移動する。

手順4: データベースをオンラインにする

ファイルの物理的な移動が完了したら、データベースをオンラインに戻す。

SSMSを使用する場合:

オブジェクトエクスプローラーで、対象のデータベースを右クリック
「タスク」 > 「オンラインにする」を選択

T-SQLを使用する場合:

USE master;
GO
ALTER DATABASE YourDatabaseName SET ONLINE;
GO

注意: YourDatabaseName は対象のデータベース名に置き換えること。

SQL Serverは、手順2で設定した新しいパスにあるファイルを認識してデータベースを起動する。

移動後の確認作業：忘れずにチェック！

どちらの方法で移動した場合でも、以下の確認作業を行うこと。

1. ファイルパスの確認

SSMSのデータベースプロパティの「ファイル」ページ、または以下のT-SQLで、MDFファイルとLDFファイルのパスが新しい場所に正しく変更されていることを確認する。

USE YourDatabaseName;
SELECT name, physical_name FROM sys.database_files;

2. データベースの動作確認

アプリケーションからデータベースに正常に接続できるか、簡単なSELECTクエリなどを実行してデータが読み取れるかなど、データベースが問題なく機能していることを確認する。

3. 古いファイルの削除 (任意だが推奨)

移動が正常に完了し、データベースが問題なく動作していることを十分に確認できたら、元のドライブにあった古いMDFファイルとLDFファイルを削除してディスク容量を解放する。（万が一のために数日間保持しておくのも良い。）

重要な注意点

LDFファイルも一緒に移動: MDFファイル（データファイル）とLDFファイル（トランザクションログファイル）は密接に関連している。特別な理由がない限り、必ずセットで同じ手順で移動すること。
ダウンタイムの発生: いずれの方法でも、作業中はデータベースが利用できなくなる。サービスへの影響を最小限に抑えるため、計画的に実施すること。
テスト環境での事前検証: 本番環境で作業を行う前に、必ずテスト環境で一連の手順を試し、問題がないことを確認すること。
SQL Serverサービスアカウントの権限: 移動先のフォルダに対するSQL Serverサービスアカウントのアクセス権限は非常に重要だ。不足していると、アタッチ時やオンライン化時にエラーが発生する。

MDFファイルの肥大化への根本対策（補足）

今回のドライブ変更はストレージの問題を一時的に解決するが、MDFファイルがなぜ肥大化したのか、その根本原因に対処することも長期的な安定運用には重要だ。

肥大化の一般的な原因:

データの継続的な追加・更新
大きな一時テーブルや作業領域の使用
インデックスの再構築や再編成による余剰領域
削除されたデータ領域が物理的に解放されていない（内部フラグメンテーション）

ファイルの縮小 (Shrink) 操作

重要な警告：Shrink操作は本番環境では最終手段として位置付けること

ファイル内に未使用の領域が多く存在する場合、DBCC SHRINKFILE や DBCC SHRINKDATABASE コマンド（またはSSMSのGUI）を使用してファイルサイズを縮小できる。

しかし、Shrink操作は深刻なリスクを伴う。特に本番環境では極力避けるべきであり、実行する場合は最終手段として慎重に検討すること。

重大なリスクと注意点:

深刻なパフォーマンス劣化: ファイルの縮小はデータの物理的な再配置を伴い、深刻なI/O断片化（フラグメンテーション）を引き起こす。これにより、クエリのパフォーマンスが大幅に低下し、場合によっては業務に支障をきたすレベルになる可能性がある。
長時間のリソース消費: 縮小操作は大量のI/Oリソースを消費し、他の処理に影響を与える。本番環境では必ずメンテナンス時間に実施すること。
必須のインデックス再構築: 縮小を行った場合は、必ず主要なテーブルのインデックスを再構築または再編成する必要がある。これを怠ると、パフォーマンス問題が長期間継続する。
頻繁な縮小は厳禁: 定期的にファイルが大きくなり、それを都度縮小するのは最も避けるべきパターンだ。適切な初期サイズ設定や自動拡張設定を見直し、根本的な解決を図ること。

本番環境でのShrink実行前チェックリスト: