先ほど投稿した内容の続きになります。
データファイルを追加してデータを平準化すると各ファイルの使用状況は以下のようになります。
何かの理由で、追加したデータファイルを削除する必要が発生し、削除をしようとすると以下のメッセージが表示され削除をすることができません。
DataFile ‘TEST2’ の削除に失敗しました。 (Microsoft.SqlServer.Smo) |
メッセージに表示されているように、すでにデータが格納されているファイルは空ではありませんので削除をすることができません。
データを空にするためには、[EMPTYFILE] を指定してファイルを圧縮する必要があります。
SQL で実行する場合は以下のクエリを実行します。
USE [TEST] |
SSMS で圧縮をする場合は、ファイル単位の圧縮で [データを同じファイル グループの他のファイルに移行してファイルを空にする] を選択してファイルの圧縮を行います。
いずれかの操作をすることで対象のファイルから別のファイルにデータを移行することが可能です。
EMPTYFILE を指定してデータベースの圧縮をすると以下のような結果が表示されます。
UsedPages は 0 となっていますが、SHOW FILESTATS の結果では 1 エクステントが使われています。
# 管理用のページが残っているからだからだと思いますが。
EMPTYFILE により圧縮をしたファイルにはデータが格納されていません。
以下は BOL に記載されている内容です。
指定したファイルから、同じファイル グループ内の他のファイルにすべてのデータを移動します。データベース エンジンではデータを空のファイルに配置できなくなったので、ファイルを削除するには、ALTER DATABASE ステートメントを使用します。
圧縮により空にしたファイルにはデータの配置ができなくなりますので、データを追加しても使用されなくなります。
# ページヘッダ と sys.database_files を確認してみたのですが違いがいまいちわかりませんでした…。
SSMS から見るとファイルが削除されているのですが、[sys.database_files] を確認すると実はファイルが削除されていません。
削除しただけでは、[OFFLINE] の状態でエントリとしては残った状態となっています。
SSMS のファイルの表示は [state] が [0] (ONLINE) または [2] (RECOVERING) のファイルが表示されるようになっています。
# 実際には sys.database_files ではなく、sys.master_files から取得しています。
そのため、SSMS では表示はされないがエントリとしては残った状態となります。
エントリが残った状態で同じ名前 (TEST2) でファイルを追加してみます。
データベース ‘TEST’ のAlterに失敗しました。 (Microsoft.SqlServer.Smo) Transact-SQL ステートメントまたはバッチの実行中に例外が発生しました。 (Microsoft.SqlServer.ConnectionInfo) 次回の BACKUP LOG 操作が終了するまで、ファイル ‘F:DataTEST2.ndf’ を再利用できません。 |
メッセージに表示されている通りなのですが、トランザクションログのバックアップをしないとエントリが削除されないためエラーが発生しています。
エントリが存在している状態では、[is_name_reserved] が [1] となっています。
BOL には以下のように記載されています。
1 = 削除されたファイル名を再使用できます。新しいファイル名に対して名前 (name または physical_name) を再使用するには、ログのバックアップを実行する必要があります。
0 = ファイル名は再使用できません。
[is_name_reserved] が [1] の状態について明記がされていますね。
それではトランザクションログのバックアップを取って再度確認をしてみたいと思います。
トランザクションログのバックアップを取得することでファイルのエントリが削除されていることが確認できます。
ファイルの削除をする際にはトランザクションログのバックアップをして [sys.database_files] または、[sys.master_files] からエントリが削除されるところまでを確認しておいた方が良いかもしれないですね。
[…] 追加したデータファイルからデータを移動する方法 […]
複数ファイルでデータベースを構成した場合のサイズ均等化の必要性 « SE の雑記
26 4月 11 at 08:11
[…] 追加したデータファイルからデータを移動する方法 […]
複数ファイルでデータベースを構成した場合のサイズ均等化について « SE の雑記
26 4月 11 at 08:12
[…] 追加したデータファイルからデータを移動する方法 […]
複数データファイルでデータベースを構成した場合のサイズ均等化について « SE の雑記
26 4月 11 at 08:13