SE の雑記

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

トランザクションログが壊れるとバックアップまで戻すしかない??

2 comments

いろいろと話題になっている [中堅企業向け オラクル都市伝説- シーズン2] ですが、記事の中に以下の記載があります。

ログファイルが壊れるとデータの復旧ができない。
毎日夜中にバックアップを取っていたが、データはバックアップした状態、すなわち昨日の夜の時点に戻ってしまう。
ユーザーがショッピングサイト上で行っていた直近の購入情報が損失してしまうのだ。ショッピングサイト運営会社は夜間に社員を緊急収集し、損失したデータを手作業で入力するはめになった。その他、クレーム対応のため、膨大な損失を被むる。
まさに地獄である。

 

ログファイルが壊れ、データの復旧ができなくなったため、バックアップまで戻したとあります。

トランザクションの整合性が保たれた状態に戻すということを考えるとこの対応は正しいと思いますが、
障害発生時では、それまでのデータを何とかして確認する方法がないかということも重要だと思います。

SQL Server はトランザクションログファイルが壊れてもデータファイルが生きていれば状況によってはある程度は復旧できます。

といことで実験してみました。

[トランザクションログを壊す]

まずは、トランザクションログを壊さないと話が始まりません。
とりあえず、バイナリエディタを使用してファイルの内容を 0 クリアしてみました。
データベースは SQL Server 2000 の Northwind を使用しています。
 

[データベースは障害状態になる?]

この状態のデータベースを SQL Server 2000 で開くとどうなるか確認してみました。
image

データベースが開けちゃうんですね・・・。
DBCC で確認してもエラーにはなりませんでした。

[トランザクションログのファイル自体を削除する]

続いては ldf を削除してデータファイルだけの状態にしてみました。
これもまた起動してきちゃうんですね。

image

SQL Server が起動時に新しいトランザクションログを作っていました。

image

[トランザクションログファイルを壊れた状態にするには?]

壊さないことには検証が始まらなさそうですので、ひとまず復旧間隔を 20 分に設定し、10,000 件のデータを挿入して、
サーバーを強制シャットダウンして、トランザクションログを FF でクリアしてみました。

どうやらトランザクションログを壊すことに成功したようです。
# SQL Server 2000 / 2008 でこの方法でログを壊すことができました。
 image

さて、これで都市伝説と同じ状態に持っていくことができました。
ここから、データを復旧したいと思います。

[ログの再構築でデータを復旧]

SQL Server 2000 と 2005 以降ではログの復旧方法が異なります。

[SQL Server 2000]
SQL Server 2000 で復旧する場合は、システムテーブルを直接更新して、EMERGENCY モードに設定する必要があります。
SQL Server 2000 の DBCC にはログを再構築するコマンドがあります。
REBUILD_LOG コマンドで新しいログファイルが作成できます。このコマンドは Undocumented な DBCC なんですよね。

2000 では REPAIR_ALLOW_DATA_LOSS でログファイルを再構築することができないので、REBUILD_LOG コマンドを
使用してログファイルの再構築をする必要があります。

sp_configure ‘allow updates’,1
GO
RECONFIGURE WITH OVERRIDE
GO
UPDATE sysdatabases SET status = 32768 WHERE name = N’Northwind’
GO

ALTER DATABASE [Northwind] SET SINGLE_USER
GO
DBCC REBUILD_LOG(‘Northwind’,’C:Program Files (x86)Microsoft SQL ServerMSSQL$SQL2000DataNorthwind.LDF’)
GO
ALTER DATABASE [Northwind] SET MULTI_USER
GO
sp_configure ‘allow updates’,0
GO
RECONFIGURE WITH OVERRIDE
GO

設定オプション ‘allow updates’ が 0 から 1 に変更されました。RECONFIGURE ステートメントを実行して、インストールしてください。

(1 行処理されました)
警告 : データベース ‘Northwind’ のログが再構築されました。トランザクションの一貫性は失われます。DBCC CHECKDB を実行して物理的な一貫性を調べる必要があります。データベース オプションを再設定し、余分なログ ファイルを削除する必要があります。
DBCC の実行が完了しました。DBCC がエラー メッセージを出力した場合は、システム管理者に相談してください。
設定オプション ‘allow updates’ が 1 から 0 に変更されました。RECONFIGURE ステートメントを実行して、インストールしてください。

[SQL Server 2005 以降]
SQL Server 2005 以降では ALTER DATABASE で EMERGENCY モードに設定することができます。
また、2005 以降では DBCC CHECKDB を REPAIR_ALLOW_DATA_LOSS で実行することとで
トランザクションログの再作成ができます。
# REBUILD_LOG は使えなくなっています。

ALTER DATABASE [Northwind] SET EMERGENCY
GO
ALTER DATABASE [Northwind] SET SINGLE_USER
GO
DBCC CHECKDB (‘Northwind’, REPAIR_ALLOW_DATA_LOSS)
GO
ALTER DATABASE [Northwind] SET MULTI_USER
GO

 

ファイル アクティブ化エラー。物理ファイル名 "C:Program FilesMicrosoft SQL ServerMSSQL10.SQL2008MSSQLDATAnorthwnd.ldf" が正しくない可能性があります。
データベースのシャットダウン時に開いているトランザクション/ユーザーがあったか、データベースにチェックポイントが発生していないか、またはデータベースが読み取り専用のため、ログを再構築できません。このエラーは、トランザクション ログ ファイルが手動で削除されたか、ハードウェアまたは環境の障害により失われた場合に発生する可能性があります。
警告: データベース ‘Northwind’ のログが再構築されました。トランザクションの一貫性は失われました。RESTORE チェーンが壊れ、サーバーが以前のログ ファイルのコンテキストを保持しなくなったので、以前のログ ファイルについて把握しておく必要があります。DBCC CHECKDB を実行して物理的な一貫性を検証してください。データベースは dbo 専用モードに設定されました。データベースが使用可能な状態になったら、データベース オプションを再設定し、余分なログ ファイルを削除してください。
‘Northwind’ の DBCC 結果。
Service Broker メッセージ 9675、状態 1: 分析されるメッセージ型: 14。
Service Broker メッセージ 9676、状態 1: 分析されるサービス コントラクト: 6。
Service Broker メッセージ 9667、状態 1: 分析されるサービス: 3。
Service Broker メッセージ 9668、状態 1: 分析されるサービス キュー: 3。
Service Broker メッセージ 9669、状態 1: 分析されたメッセージ交換のエンドポイント: 0。
Service Broker メッセージ 9674、状態 1: 分析されたメッセージ交換グループ: 0。
Service Broker メッセージ 9670、状態 1: 分析されるリモート サービス バインド: 0。
Service Broker メッセージ 9605、状態 1: 分析されたメッセージ交換の優先度: 0。
‘sys.sysrscols’ の DBCC 結果。
オブジェクト "sys.sysrscols" の 10 ページには 785 行あります。

~ 省略 ~

‘Suppliers’ の DBCC 結果。
オブジェクト "Suppliers" の 1 ページには 29 行あります。
CHECKDB により、データベース ‘Northwind’ に 0 個のアロケーション エラーと 0 個の一貫性エラーが見つかりました。
DBCC の実行が完了しました。DBCC がエラー メッセージを出力した場合は、システム管理者に相談してください。
メッセージ 824、レベル 24、状態 2、行 1
SQL Server で、一貫性に基づいた論理 I/O エラーが検出されました: 無効な保護オプション。このエラーは、ファイル ‘C:Program FilesMicrosoft SQL ServerMSSQL10.SQL2008MSSQLDATAnorthwnd.ldf’ のオフセット 0000000000000000 にあるデータベース ID が 7 のページ (2:0) の 読み取り 中に発生しました。SQL Server エラー ログまたはシステム イベント ログ内の別のメッセージで詳細情報が報告されることもあります。このエラー状態は深刻で、データベースの整合性を損なう可能性があるので、すぐに解決する必要があります。完全なデータベース一貫性確認 (DBCC CHECKDB) を実行してください。このエラーには多くの要因があります。詳細については、SQL Server オンライン ブックを参照してください。

このような形でログファイルが破損していても、データファイルのデータは復旧することが可能です。

SQL Server 2000 / 2005 / 2008 ではログの再構築により、トランザクションログが破損していても
データファイル上のデータを復旧することができます。

ただし、ダーティーページに関してはデータファイル上に書き込みがされていませんので、このようなデータは
トランザクションログだけに書き込まれており、データファイル上には存在しません。

この辺はチェックポイントの発生タイミングに依存しますので、復旧間隔の設定によってどの程度の
データがデータファイルに書き込まれているかが変わっていきます。

[データファイルだけをアタッチ]

SQL Server 2000 以降では sp_attach_singile_file_db というデータファイルだけをアタッチする
ストアドプロシージャがあります。
ログファイルが破損した状態のデータファイルだけをアタッチしようとしたところこの方法は
うまくいきませんでした。

CREATE DATABASE でアタッチする方法も同様でエラーとなってしまいました。

SQL Server 2005 以降では復旧中になっているデータベースはデタッチできないようになっているので、
間違ってエントリをはずすことはないかとは思いますが。

SQL Server 2000 では sp_attach_single_file_db による、単一のデータファイルによるアタッチしか、
データファイルをアタッチする方法がありませんが、状況によってはデータファイルのアタッチによる
復旧ができる可能性もあるかと思います。
# 今回、私は失敗しましたが・・・。

ログファイルが二重化できないのである程度の復旧となってしまいますので、トランザクションログの構成については
Oracle に優位性があるのは確かだと思います。

ただし、SQL Server ではバックアップ取得時点でなくても途中までは復元できる可能性は残っています。
# DBCC でログを再構築した場合はログチェーンも切れてしまうと思いますので、トランザクションログの
  バックアップ運用をしていても直前までは復元できないと思います。

よい機会だったので、復元のメモ書きとして投稿しておきます。

Written by masayuki.ozawa

9月 4th, 2009 at 4:26 pm

Posted in SQL Server

2 Responses to 'トランザクションログが壊れるとバックアップまで戻すしかない??'

Subscribe to comments with RSS or TrackBack to 'トランザクションログが壊れるとバックアップまで戻すしかない??'.

  1. 私も同様な方法(ログ削除)などでトラブル対応を行った実績があります。最近良く感じるのが、トラブル時のデータ復元についてどの時点まで戻す必要があるか検討していないことが多いということです。前日のバックアップまで戻し、処理を再実行すれば問題なしといったパターンが多い割に、データベースタイプを「完全」にしている、その上しっかり管理出来ずログがあふれる。こういったところが多いのが実際だと思います。いろいろ検討する必要はあると思いますが、管理出来ないようならタイプを「単純」にするという選択肢もありかと思います。※そのあたりがわかってればしっかりログコンデンスするのでしょうけど….

    正樹

    8 9月 09 at 01:14

  2. >最近良く感じるのが、トラブル時のデータ復元について>どの時点まで戻す必要があるか検討していないことが多いということです。>前日のバックアップまで戻し、処理を再実行すれば問題なしといったパターンが多い割に、>データベースタイプを「完全」にしている、その上しっかり管理出来ずログがあふれる。>こういったところが多いのが実際だと思います。私も実際の現場では、どの時点まで戻せばよいか検討されていないことは意外と多いのではと思っています。障害発生直前まで時間指定で戻す必要がないのであれば「単純」にしての運用が一番運用コストがかからずに楽ですよね。データベースを扱う立場からすると週次で完全バックアップ、日次で差分バックアップ、日の中で時 / 分単位でトランザクションログのバックアップがデフォルトではありますが。復旧モデルを「完全」にしている場合は、Tail-log Backup や、point-in-time recovery の手順も考える必要がありますので、運用方法を検討するにも単純な運用ではなくデータベース寄りのスキルが必須になり、現場の運用担当だけでバックアップ計画を立案するのは敷居がすこし高いと思います。実際は、障害が発生したら有識者を連れてきて対応するというパターンが多いのでしょうね。サーバー運用をされている方が自分で対応できるのがベストだとは思いますが難しいのが現実かと。トランザクションログの肥大化とインデックスの断片化は現場で多い事例ですよね。SQL Server のバックアップは一度情報を整理したいなと思って、やることリストにはタスクとして登録してはいるのですが手つかずです…。

    真之

    8 9月 09 at 22:49

Leave a Reply

*