none
SQL Server、トランザクションログの自動拡張を無効にした場合 RRS feed

  • 質問

  • SQL Server 2008 R2 Expressを使っています。

    初期値のままで使っていたのですが、4台から10万件ずつ削除するクエリーを実行すると、多分トランザクションログの拡張ロックでクエリーが待たされ、プログラム内部で10回リトライしてますが、結局3台がエラーで削除できませんでした。

    そこで一度1GBくらいになっていたトランザクションログを100BMに圧縮して自動拡張を無効にしました。

    ただ10万件削除するクエリーを実行するとエラーになりますが・・・・

    個人的には自動拡張せずに100MBに固定した場合、こう思っていました。

    ・1度に書き込むログが100MBを超えるとエラー。

    ・1度に書き込むログが100MB以内だと、書き込まれて、最後までいくとサイクリックに最初からまた書き込む。

    が、あるサイトで「自動拡張を無効にするのは絶対にお勧めしない」というのを見たのがきっかけでテストプログラムを作って検証してみました。

    その結果、どうもこういう感じに思えるんですが正しいでしょうか?

    ・1度に書き込むログが100MBを超えるとエラー。

    ・1度に書き込むログが100MB以内で、まだ書き込むエリアがあれば書き込む。

    ・1度に書き込むログが100MB以内で、書き込むエリアが足らなければ書き込まない(書き込めなかったエラーは発生しない)。

    どんどんINSERTしてテストしていると、あるタイミングでデータファイルはタイムスタンプ更新されるのに、ログファイルはタイムスタンプが更新されない状態なので、仕様としてはこうなのかと思ってますが正しいでしょうか?

    もしそうなら「自動拡張を無効にするのは絶対にお勧めしない」というのはよく分かりませんが、1度に書き込むログが大きい(今回は10万件の削除)で必要なファイルサイズに余裕を持たせたログファイル容量で固定し、自動拡張を無効にするのが、どんどんログファイルも肥大化せずにいいのではと思ったのですが。

    2012年10月4日 5:24

すべての返信

  • こんにちは。
    下記、二つの質問と理解しました。
     Q1:トランザクションログが満杯になった時の動作仕様
     Q2:トランザクションログの自動拡張に関する考慮事項

    個別の事情を理解しきれていないかもしれませんので、
    外していた時はご容赦ください。

    A1
    満杯になった際の挙動は以下に記載があります。

    満杯になったトランザクション ログのトラブルシューティング (SQL Server エラー 9002)
    http://msdn.microsoft.com/ja-jp/library/ms175495.aspx

      データベースがオンラインの時にログが満杯になると、データベースはオンラインのままですが、読み取り専用になり、更新できません。

    このことから、更新処理を行うと読み取り専用データベースであるというエラーが発生します。
    実際にどのようなテストプログラムを作成して確認されたのかはわかりませんが、
    仕様上は上記の通りとなります。

    A2
    Expressを利用されているとのことですので、
    復旧モデルは"単純"であると想定して回答します。

    単純復旧モデルの場合、トランザクションログは自動的に再利用されるため、
    ほとんどの場合、トランザクションログが自動拡張されることはありません。
    しかし、トランザクションログの再利用ができない場合に、自動拡張が行われるケースがあります。

    トランザクションログの再利用待ちについては、トランザクションログの容量をこえる大量更新によるものの他に、
    SQL Serverの内部動作による再利用待ちも含まれます。
    そのため、アプリケーションが発生させるトランザクションログの量を完全に洗い出したとしても、
    動作状況によっては予期せぬ自動拡張が発生してしまう事が想定されます。
    このことから、自動拡張を無効にするのはお勧めしないということになります。

    また、自動拡張に任せ切る設計についても推奨はされません。
    自動拡張の構成については、注意事項が以下にまとまっています。

    [INF] SQL Server における自動拡張および自動圧縮の構成に関する注意事項
    http://support.microsoft.com/kb/315512/ja

    • 回答の候補に設定 佐伯玲 2012年10月17日 6:03
    2012年10月15日 12:36
  • レスありがとうございます。

    「データベースがオンラインの時にログが満杯になると、データベースはオンラインのままですが、読み取り専用になり、更新できません。

    このことから、更新処理を行うと読み取り専用データベースであるというエラーが発生します。」

    という情報は私も検索して見つけました。

    ただ不思議なのはテストプログラムではログは1MBの拡張なしの固定サイズです。

    その状態で大きなレコードをどんどんINSERTしていってるのですが、読み取り専用ならあるタイミングでエラーが出るものと思ってたんですけど、

    何万件とINSERTしてもエラーは発生せず、データもちゃんと作成されてます。

    そのためこの「読み取り専用」というのが不思議な情報でした。

    この時にログファイルのファイルスタンプを見ると更新されていないので、

    ・1度に書き込むログが1MB以内(今回のテスト)で、書き込むエリアが足らなければ書き込まない(書き込めなかったエラーは発生しない)。

    と思ったのです。

    データは出来てるので「読み取り専用」にはならずログ書き込みだけがスキップするような処理なのだと思ってます。

    もちろんINSERTした時にデータファイルのタイムスタンプは更新されています。

    このことから、マイクロソフトの情報と食い違う気がしてよく分からなくなってしまいました。

    2012年10月17日 9:09
  • こんにちは。

    質問から読み取れる事実としての情報が少ないため、
    可能であれば、「どのような構造のテーブル」に対して、「どのようなデータ」を、「何件」INSERTしているのか、
    また、トランザクションはどの単位なのか書いていただけると追加の回答が付きやすいのではと思います。

    以下、憶測ですが、

    >その状態で大きなレコードをどんどんINSERTしていってるのですが、読み取り専用ならあるタイミングでエラーが出るものと思ってたんですけど、
    >何万件とINSERTしてもエラーは発生せず、データもちゃんと作成されてます。

    ここについては、トランザクションがINSERT文単位になっているため、
    トランザクションログが再利用され、データが登録できている可能性があります。

    1トランザクション内でトランザクションログを超過するようなデータ登録を行った場合、
    ログがいっぱいになった旨のエラーが返ってきます。
    下記サンプルをSQL Server Management Studioで実行することで確認できます。
    (8万文字については、手元で置換してください。)

    CREATE TABLE tab_log(
        key_elem int,
        dat_elem nvarchar(max)
    )
    GO
    
    BEGIN TRANSACTION
    INSERT INTO tab_log VALUES (1, '<2バイト文字を8万文字>')
    INSERT INTO tab_log VALUES (2, '<2バイト文字を8万文字>')
    INSERT INTO tab_log VALUES (3, '<2バイト文字を8万文字>')
    


    なお、トランザクションログの役割上、データ登録を行えた状況おいてログへの書き込みを行っていない、
    という状況は起りえません。
    (トランザクションログへ先に変更内容が書き込まれます。)

    • 回答の候補に設定 佐伯玲 2012年10月24日 5:49
    2012年10月22日 9:57
  • レスありがとうございます。
    別件で多忙になり、見る機会がなく遅くなりました。
    レスからだいたいの事が判明した気がするので書き込みさせてもらいます。
    まずは、テストで使っているテーブル等の詳細を書いておきます。

    <テーブル構造> キー等は省略して項目内容のみです。
    CREATE TABLE tmp_log_test(
        [id] [int] IDENTITY(1,1) NOT NULL,
        [data] [nchar](4000) NULL,
        [no] [int] NULL,
    )

    <どのようなデータを何件INSERTしているか>
    C#でボタンクリックすると下記のコードが実行するようになってます。

    string str = "あいうえお";
    for (i = 0; i < 1000; i++)
    {
        sql = "INSERT INTO tmp_log_test VALUES('" + str + "'," + i.ToString() + ")";
        sqlCmd = new SqlCommand(sql, sqlConnect);
        sqlCmd.ExecuteNonQuery();
        sqlCmd.Dispose();
    }

    1レコード:4 + 8000 + 4 = 8004 Byte
    dataはncharの固定長なので"あいうえお"だけですが8000バイトと思います。

    8004 * 1000 = 8004000 Byte (約7.63MByte)
    トランザクションログファイルは1MB固定なので1回クリックでオーバーすると思います。

    <トランザクション>
    ここが最大のポイントのようですね。
    私はC#とSQL Serverでプログラムを組むのが初めての事なのでトランザクション命令は一切使ってません。
    トランザクションという言葉は聞いた事ありますが、C#でどうするかはまだ知らないです。

    そこから考えられる事はトランザクションログはプログラムからトランザクション命令を出した時のみ書き込まれるのでは?
    という結論です。
    これなら、INSERTした時にデータファイルのタイムスタンプは更新されているのに、
    トランザクションログファイルはタイムスタンプが更新されていないのも納得です。

    ただ、そうだとすると最初に書いたデリートがエラーになったのはなぜか分からなくなります。
    ちなみにデリートの命令は下記のとおりです。

    scn.Open();
    scms = new SqlCommand("DELETE FROM " + tableName + " WHERE nengetsu < DATEADD(month, -2, GETDATE())", scn);
    scms.ExecuteNonQuery();
    scn.Close();

    トランザクション命令は使っていないのでトランザクションログにはアクセスしない気がするのですが。
    この時削除されるレコードは10万件を超え、レコード内容もかなりの項目が設定されています。

    システムでは一切トランザクション命令は使っていないのに数ヶ月でトランザクションログファイルは1GBを超えてました。
    そしてエラーが出るのはデリートのみなのでデリート時のトランザクションログ拡張ロックが原因だと思った訳です。

    トランザクション命令使うとトランザクションログに書き込みされるとすると、
    トランザクション命令使っていないのになぜログが1GBにも増えたのかが不明になります。
    もしかして大量のレコードをDELETEすると勝手にトランザクションが動いてるということなのでしょうか?

    2012年11月8日 6:29