SE の雑記

SQL Server の情報をメインに Microsoft 製品の勉強内容を日々投稿

追加したデータファイルからデータを移動する方法

3 comments

先ほど投稿した内容の続きになります。

データファイルを追加してデータを平準化すると各ファイルの使用状況は以下のようになります。
image

何かの理由で、追加したデータファイルを削除する必要が発生し、削除をしようとすると以下のメッセージが表示され削除をすることができません。
image

DataFile ‘TEST2’ の削除に失敗しました。  (Microsoft.SqlServer.Smo)
Transact-SQL ステートメントまたはバッチの実行中に例外が発生しました。 (Microsoft.SqlServer.ConnectionInfo)
ファイル ‘TEST2’ は空ではないので、削除できません。 (Microsoft SQL Server、エラー: 5042)

メッセージに表示されているように、すでにデータが格納されているファイルは空ではありませんので削除をすることができません。

データを空にするためには、[EMPTYFILE] を指定してファイルを圧縮する必要があります。
SQL で実行する場合は以下のクエリを実行します。

USE [TEST]
GO
DBCC SHRINKFILE (N’TEST’ , EMPTYFILE)
GO

SSMS で圧縮をする場合は、ファイル単位の圧縮で [データを同じファイル グループの他のファイルに移行してファイルを空にする] を選択してファイルの圧縮を行います。
image

いずれかの操作をすることで対象のファイルから別のファイルにデータを移行することが可能です。

実行前の各ファイルの使用状況は以下のようになっています。
image

実行後は以下のようになります。
image
image

EMPTYFILE を指定してデータベースの圧縮をすると以下のような結果が表示されます。
image

UsedPages は 0 となっていますが、SHOW FILESTATS の結果では 1 エクステントが使われています。
# 管理用のページが残っているからだからだと思いますが。

この状態でデータを追加してみます。
image

EMPTYFILE により圧縮をしたファイルにはデータが格納されていません。

以下は BOL に記載されている内容です。

指定したファイルから、同じファイル グループ内の他のファイルにすべてのデータを移動します。データベース エンジンではデータを空のファイルに配置できなくなったので、ファイルを削除するには、ALTER DATABASE ステートメントを使用します。

圧縮により空にしたファイルにはデータの配置ができなくなりますので、データを追加しても使用されなくなります。
# ページヘッダ と sys.database_files を確認してみたのですが違いがいまいちわかりませんでした…。

空にしたファイルは削除することが可能となります。
image

SSMS から見るとファイルが削除されているのですが、[sys.database_files] を確認すると実はファイルが削除されていません。
image
削除しただけでは、[OFFLINE] の状態でエントリとしては残った状態となっています。

SSMS のファイルの表示は [state] が [0] (ONLINE) または [2] (RECOVERING) のファイルが表示されるようになっています。
# 実際には sys.database_files ではなく、sys.master_files から取得しています。

そのため、SSMS では表示はされないがエントリとしては残った状態となります。

エントリが残った状態で同じ名前 (TEST2) でファイルを追加してみます。
image

データベース ‘TEST’ のAlterに失敗しました。  (Microsoft.SqlServer.Smo)

Transact-SQL ステートメントまたはバッチの実行中に例外が発生しました。 (Microsoft.SqlServer.ConnectionInfo)

次回の BACKUP LOG 操作が終了するまで、ファイル ‘F:DataTEST2.ndf’ を再利用できません。
次回の BACKUP LOG 操作が終了するまで、ファイル ‘TEST2’ を再利用できません。 (Microsoft SQL Server、エラー: 1833)

メッセージに表示されている通りなのですが、トランザクションログのバックアップをしないとエントリが削除されないためエラーが発生しています。
エントリが存在している状態では、[is_name_reserved] が [1] となっています。
image

BOL には以下のように記載されています。

1 = 削除されたファイル名を再使用できます。新しいファイル名に対して名前 (name または physical_name) を再使用するには、ログのバックアップを実行する必要があります。

0 = ファイル名は再使用できません。

[is_name_reserved] が [1] の状態について明記がされていますね。

それではトランザクションログのバックアップを取って再度確認をしてみたいと思います。
image

トランザクションログのバックアップを取得することでファイルのエントリが削除されていることが確認できます。

ファイルの削除をする際にはトランザクションログのバックアップをして [sys.database_files] または、[sys.master_files] からエントリが削除されるところまでを確認しておいた方が良いかもしれないですね。

Written by masayuki.ozawa

12月 2nd, 2010 at 10:26 pm

Posted in SQL Server

Tagged with

3 Responses to '追加したデータファイルからデータを移動する方法'

Subscribe to comments with RSS or TrackBack to '追加したデータファイルからデータを移動する方法'.

  1. […] 追加したデータファイルからデータを移動する方法 […]

    複数ファイルでデータベースを構成した場合のサイズ均等化の必要性 « SE の雑記

    26 4月 11 at 08:11

  2. […] 追加したデータファイルからデータを移動する方法 […]

    複数ファイルでデータベースを構成した場合のサイズ均等化について « SE の雑記

    26 4月 11 at 08:12

  3. […] 追加したデータファイルからデータを移動する方法 […]

    複数データファイルでデータベースを構成した場合のサイズ均等化について « SE の雑記

    26 4月 11 at 08:13

Leave a Reply

*