このホームページ / ブログに利用しているホスティングサービス会社から、SQL Server データベースのログファイルのサイズが数 10 GB に肥大化していて破損している可能性があるので対応して欲しいとの連絡がありました。

自分が壊したわけではない（はず）なのですが、ホスティングサービス会社では復元のための作業はやってくれないそうなので、やむを得ず自分でやりました。その手順を備忘録として書いておきます。

まず、ホスティングサービス会社が提供している myLittleAdmin というツールを利用してバックアップを作成し、それをダウンロードします。

ファイルサイズが数 10 GB と巨大なので、バックアップファイルの作成とダウンロードにとんでもなく時間がかかるかと思っていましたが、全然そんなことはなかったです。

バックアップファイル (.bak) の作成にはほとんど時間はかかりません。生成された .bak ファイルのサイズは 38,666 KB（GB ではなくて）でした。ダウンロードには、当たり前ですが、ファイルサイズに似合った時間はかかりましたが。

.bak ファイルを C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup に保存。UserDatabase データーベースは、自分の PC の SQL Server 2008 Express に作成済み、アタッチ済みだったものがあったので、それを復元してみました。

復元作業は、SQL Server Management Studio を使って、マイクロソフト公式解説書「一目でわかる Microsoft SQL Server 2008」の 5 章の「6 データベースを復元するには」の手順に従って行いました。

しかし、デフォルト設定のままやると UserDatabase の .bak ファイルが違うという意味（正確なメッセージは忘れました）のエラーが出て復元できません。

本にはデフォルト設定のままでもよさそうに書いてありましたが、［オプション］タブの［復元オプション］の項目で［既存のデータベースを上書きする (WITH REPLACE)(O)］にチェックを入れないとダメでした。

SQL Server のバージョンの違い（ホスティングサービスは 2005、自分の開発環境は 2008）は復元には問題なかったです。

復元されたデータベースのファイルサイズは、エクスプローラで見て UserDatabase.mdf が 39,296 KB、USERDATABASE.LDF が 51,090,368 KB でした。

開発マシンの Web アプリからデータベースに接続し、ざっと内容を見てみたが、正しく復元されていてデータベースファイルには問題がない様子。

問題はやはり、ホスティングサービス会社が言うように、USERDATABASE.LDF が 51,090,368 KB と肥大化していること（だけ？）のようです。

次に、トランザクションログの切捨てを行うため、トランザクションログをバックアップします。（切捨ては、ログファイルに再利用可能な領域を広げるだけで、ファイルサイズを小さくするわけではないそうです）

マイクロソフト公式解説書「一目でわかる Microsoft SQL Server 2008」の 5 章の「3 トランザクションログをバックアップするには」の手順に従って、userdatabaselogfile_20150614.bak というファイル名を指定してバックアップ。バックアップは成功し（正常に実行されましたとのダイアログが出る）、2,954 KB の .bak ファイルが作成されました。

しかし、やはりログファイル USERDATABASE.LDF のサイズは変わりません。ファイルサイズを小さくするために DBCC SHRINKFILE (UserDatabase_log, 5) を実行してみます。

（注：ファイル名には SELECT * FROM sys.database_files で取得できる UserDatabase_log という名前を使わないとダメでした。USERDATABASE.LDF という名前を使うと sys.database_files でファイルが見つからないというエラーになります）

このときは、"クエリが正常に実行されました" とは出るものの、［メッセージ］タブをクリックして見ると、"ログ ファイル 2 (UserDatabase_log) を圧縮できません。ファイルの末尾にある論理ログ ファイルが使用中です" というエラーメッセージが出ます。理由は不明です。（汗）

DBCC SQLPERF(LOGSPACE) を実行してみると以下の結果になりました。ファイルサイズは多少は小さくなったもののまだ数 10 GB のオーダーです。

Log Size (MB): 48474.99
Log Space Used (%): 0.6307064

エクスプローラで見ると、ファイルサイズは、UserDatabase.mdf が 39,296 KB、USERDATABASE.LDF が 49,638,400 KB（DBCC SHRINKFILE 実行前は 51,090,368 KB）で、やはりサイズの縮小に失敗しています。

ここで諦めては何なので、もう一回ログファイルのバックアップからやってみるました。前回作成した userdatabaselogfile_20150614.bak に上書きしたことが異なるだけで、その他の手順は前回と同じです。前回と同様に処理は正常に終わります。.bak ファイルのサイズは 2,954 KB ⇒ 204,048 KB になりました。

DBCC SQLPERF(LOGSPACE) を実行してみると以下の結果となり、Log Size (MB) は同じながら、Log Space Used (%) が一桁下がっています。

Log Size (MB): 48474.99
Log Space Used (%): 0.04509413

DBCC SHRINKFILE (UserDatabase_log, 5) を実行してみると、今度は "圧縮できません" というエラーメッセージは出ません。再度 DBCC SQLPERF(LOGSPACE) を実行してみると以下の結果となり、Log Size (MB) が大幅に下がっていました。

Log Size (MB): 5.117188
Log Space Used (%): 14.37023

DBCC SHRINKFILE (UserDatabase_log, 5) の 5 というのは、ファイルサイズが 5MB になるよう領域を開放することを意味するので、ほぼ期待通りの結果です。

何故一回目がうまく行かなかったのかは謎です。（汗）

開発環境でこの記事をポストして正常に表示されることを確認してから、SQL Server Management Studio で .bak ファイルを作成し、myLittleAdmin を利用して本番環境の UserDatabase を復元・・・を試みましたが、"デバイス 'xxx.bak' のメディア ファミリが正しい形式ではありません。SQL Server はこのメディア ファミリを処理できません" というエラーメッセージが出て失敗に終わります。（涙）

心当たりがないか、ホスティングサービス会社に問い合わせ中です。SQL Server 2008 で作成したバックアップファイルで SQL Server 2005 のデータベースの復元は間違いなくできるのでしょうか？　（バージョンの違いの件は、事前にホスティングサービス会社に問合せていて、「恐らく大丈夫」とのことでしたが・・・）

【2015/6/16 追記】

本番環境の UserDatabase の復元に失敗する理由は、やはり SQL Server のバージョンの違いでした。

MSDN ライブラリの記事 復元と復旧の概要 (SQL Server) の「注意」に "SQL Server 2008 のバックアップを以前のバージョンの SQL Server で復元することもできません" とはっ��り書いてありました。

ホスティングサービス会社のサポート担当の人によると、彼ら自身で実際に SQL Server 2008 R2 で作業して復元は可能なことは確認したとのことでしたが、それは SQL Server 2008 R2 では更新等何も行わなかったからで、更新を行うと同じエラーとなって失敗に終わるそうです。

結局、自分の開発マシンに SQL Server 2005 Developer Edition をインストールして、上に書いたのと同様な手順で復元作業を行った結果、myLittleAdmin の復元ウィザードで SQL Server 2005 Std のデータベースの復元ができました。

ただし、修復用の .bak ファイルを作った時のログファイルのサイズは 5MB 程度だったのが、時間と共に増えていっているのが気がかりです。

myLittleAdimin で SELECT * FROM sys.database_files クエリを実行して調べたログファイルの size は、復元した時: 5MB ⇒ その約 11 時間後: 48MB ⇒ その約 1 時間後: 64MB ⇒ その約 1 時間後: 70MB と増えていくのが解せません。

ちなみに、データベースファイルの size は 4912（x 8 = 39,294 KB）でローカル環境から変化していません。また、復元した UserDatabase に対して DBCC CHECKDB を実行してみましたが、エラーなしという結果でした。

何かまだ問題があるような気がしています。ホスティングサービス会社に、サーバーに障害や設定上の問題等はないか確認依頼中です。

上に、「何故一回目がうまく行かなかったのかは謎です」と書きましたが、SQL Server 2005 を使って作業した時も同じで、その原因はログファイルが壊れていて、そこが修復できていないからではないかという気もしています。

【2015/6/17 追記】

上の【2015/6/16 追記】に書きましたように、ホスティングサービス会社にサーバーに障害や設定上の問題等はないか確認をお願いしていましたが問題ないとの返答がありました。

昨日はデータベースの更新は行ってないのにログファイルの size がどんどん増えていくのが解せなかったのですが、今日 size を調べてみたところ落ち着いてきていました。もう少し様子を見て、まだ問題があるようでしたらまたここに書くことにします。