none
SQL Server Disributionデータベース肥大化について RRS feed

  • 質問

  • 概要

    SQL Server 2014 にて、別サーバとのレプリケーションのため、ディストリビュータの設定を行い稼働させています。
    しかし、別システムの不手際で設定していた時に使用していたsa権限のユーザーから、sa権限がはく奪され、
    ディストリビューションのクリーンアップジョブがずっとエラーとなってしまいました。

    気が付いた時には、distributionデータベースが肥大化し、2TBほどに膨れ上がりました。
    この膨れ上がったデータを削除したいのですが、極力システムは止めたくありません。
    一度、ディストリビュータの設定解除を行えば綺麗に削除されることはわかりましたが、30時間以上かかります。
    システムを停止できないので、なにか他に手立てがないか教えてください。

    また肥大化したdistributionデータベースのゴミデータ削除をデータベースの停止などはさせずに行いたいです。

    ※レプリケーションの張り直しくらいはしょうがないと考えていますが。。。

    2021年4月20日 4:52

回答

  • クリーンアップ ジョブのストアドは、一度に大量のレプリケーション コマンド/トランザクション を削除する場合、一般的に負荷が高くなるストアドになるため、影響を及ぼす可能性はあるかもしれません。

    パラメータ値を変更して実行したとしても、クリーンアップ ジョブのストアドに時間を要したとのことですが、削除対象のトランザクションを確認する処理で時間を要していることが推測されます。

    なお、削除対象のトランザクションが特定された後、削除処理が行われますが、この削除処理で一度に大量のレコードを削除すると、トランザクションログが肥大化し、最悪 トランザクションログを拡張することができず、SQL Server インスタンスが停止してしまう可能性が危惧されます。

    そのため、クリーンアップ ジョブのストアドを実行する場合は、上記のような現象を発生させないように、パラメータ値を調整しながらデータを削除する必要があると思います。


    • 回答としてマーク hy.v13.21 2021年7月21日 6:37
    2021年4月22日 9:35

すべての返信

  • ディストリビューションのクリーンアップジョブでは、以下のストアドプロシージャが実行されています。 レプリケーションの構成を維持しつつ、MSrepl_transactions、MSrepl_commands のデータを削除するためには、やはり ディストリビューションのクリーンアップ ジョブを実行する必要があると思います。

    しかしながら、大量のレコードが挿入され肥大化した ディストリビューション 上で以下のコマンドを実行すると、一度に大量のレコードが削除されることにより、ディストリビューション データベースのトランザクションログファイルが肥大化することが危惧されます。

    どれくらいの期間、クリーンアップ ジョブが止まっていたかは分かりませんが、以下のストアドプロシージャの @min_distretention, @max_distretention のパラメータ値 (単位: 時 (h)) を明示的に指定して、削除されるデータが最小限になるようにしたうえで、データを削除していくということはできるかもしれません。

    ただ、本対処方法も少しづつデータを削除していく分、非常に時間を要する作業にはなるかと思います。

    EXEC dbo.sp_MSdistribution_cleanup @min_distretention = 0, @max_distretention = 72

    sp_helptext コマンドにより、該当のストアドプロシージャの中身を確認でき、該当ストアドプロシージャの中でさらにsp_MSdistribution_delete が実行されています。

    必要におうじて、sp_helptext などにより、実行されているストアドプロシージャの中身を確認し、今後の対処方法を検討されても良いかもしれません。

    use distribution
    go
    sp_helptext 'sp_MSdistribution_cleanup'
    go


    2021年4月20日 15:14
  • ご回答ありがとうございます。

    いただいたストアドは最初に実行してみましたが、確かに72時間以前のデータを対象にするとかなりの時間がかかります。

    例えば、distibutionデータベースのMDF、LDFを直接削除するようなことはできないでしょうか?

    SQL Server を停止して、デタッチを行い、MDFとLDFのファイル名変更を行うなどができれば直接削除可能かと考えました。

    ただシステムDBなので、そのまますべて消してはいけないのでは?と考えています。

    2021年4月22日 3:08
  • 残念ながら、distibution データベースの MDF/LDF を直接削除する方法はないかと思います。

    既に検証されていると思いますが、レプリケーションを無効化(すべてのレプリケーションを削除) することが可能なのであれば、ディストリビューション データベースも初期化されるため、初期サイズにすることはできるかとは思います。

    なお、dbo.sp_MSdistribution_cleanup のパラメータ @min_distretention、@max_distretention の値は、SSMSなどで手動でクエリを実行することで、明示的にパラメータ値を指定して実行することが可能です。

    例えば @max_distretention の値に 14400 (240日前) などの大きな値を指定し、削除される MSrepl_transactions、MSrepl_commands のデータ量を減らして、SQL Server 全体に及ぼす影響を軽減させつつ、@max_distretention の値を少しづつ減らしながら、ディストリビューション上のデータを徐々に減らしていくという手法は取れるかと思います。

    そして、データの削除が完了した後、ディストリビューション データベースに対して、データベースのシュリンク処理を実施することで、distibution データベースの MDF/LDF ファイルサイズを縮小されることができるかと思います。

    2021年4月22日 3:29
  • ご教示いただきありがとうございます。

    ストアドのパラメータについても、2年前の日付が対象になるように実行など行っておりましたが、

    対象がなくてもかなり時間がかかるようです。

    ひとつ、対応方法を考えたのですが、以下内容で懸念やご指摘がありましたらご教示ください。

    <対応案>

    前提:

    サーバA⇒現在のディストリビュータ、サブスクライバ

    サーバB⇒パブリッシャ ※ディストリビュータなし

    B⇒Aへレプリケーションが行われていて、統括しているのはサーバAという状態です。

    ①サーバBのレプリケーションを一旦すべて削除

    ②サーバBへディストリビュータの設定を行う

    ③サーバBから、ディストリビュータBを使用してレプリケーションを張り直し

    ④レプリケーションがうまくいっていることを確認出来たら、サーバAのディストリビューション無効化実施

    ⑤時間はかかるがサーバAの肥大化が解消される。レプリケーションはサーバBにて稼働のまま。

    <懸念点>

    ④から⑤の間にディストリビューション無効化が行われますが、サーバBで稼働しているレプリケーション、

    および通常のSQL動作として問題がないでしょうか?

    なにか他に懸念点などあれば教えてください。

    また認識に間違いなどがあれば合わせて教えていただけますと助かります。


    2021年4月22日 4:19
  • 追記致します。

    72時間以前のデータを対象にクリーンアップを実行し、シュリンクを行った結果は下記です。

    クリーンアップ ストアド:10日間実行(10日動かしても完了しなかったため、途中停止)

    この状態でシュリンク:Total 2TB中 60GBが削除(おそらくロールバックされているのかと考えました。)

    このクリーンアップストアドの実行について、稼働中は特に影響などはないと考えていいのでしょうか?

    負荷が多少かかる、などはあると思いますが、システムに影響を出したくないので念のため確認です。

    2021年4月22日 4:35
  • サーバ A : リモート ディストリビュータ + サブスクライバ、サーバ B : パブリッシャ の状態で、レプリケーションの削除(パブリケーション、サブスクリプション) を実施し、サーバ A : リモート ディストリビュータ から サーバ B のパブリッシャ情報を削除する前提であれば、技術的には問題ない方法だと思います。

    気になる点としては、ディストリビューション DB が サーバ B (パブリッシャ) 側に作成されることで、レプリケーション コマンド/トランザクションの書き込み、クリーンアップ ジョブの負荷が サーバ B に加わることになる点でしょうか。 

    また、サーバ A 上で リモート ディストリビュータを削除する際、以下のように手動による削除が必要になるかもしれません。

    ディストリビューターを削除する

    2021年4月22日 9:27
  • クリーンアップ ジョブのストアドは、一度に大量のレプリケーション コマンド/トランザクション を削除する場合、一般的に負荷が高くなるストアドになるため、影響を及ぼす可能性はあるかもしれません。

    パラメータ値を変更して実行したとしても、クリーンアップ ジョブのストアドに時間を要したとのことですが、削除対象のトランザクションを確認する処理で時間を要していることが推測されます。

    なお、削除対象のトランザクションが特定された後、削除処理が行われますが、この削除処理で一度に大量のレコードを削除すると、トランザクションログが肥大化し、最悪 トランザクションログを拡張することができず、SQL Server インスタンスが停止してしまう可能性が危惧されます。

    そのため、クリーンアップ ジョブのストアドを実行する場合は、上記のような現象を発生させないように、パラメータ値を調整しながらデータを削除する必要があると思います。


    • 回答としてマーク hy.v13.21 2021年7月21日 6:37
    2021年4月22日 9:35
  • お回答ありがとうございます。

    とても参考になりました。

    1点だけ、確認させてください。

    お送りした以下の内容については、どうでしょうか?

    なにか注意すべき点があれば教えていただけませんでしょうか。

    ><懸念点>

    >④から⑤の間にディストリビューション無効化が行われますが、サーバBで稼働しているレプリケーション、

    >および通常のSQL動作として問題がないでしょうか?

    2021年4月23日 4:10
  • hy.v13.21さん、こんにちは。フォーラムオペレーターのHarukaです。
    MSDNフォーラムにご投稿くださいましてありがとうございます。

    >①サーバBのレプリケーションを一旦すべて削除
    ②サーバBへディストリビュータの設定を行う
    ③サーバBから、ディストリビュータBを使用してレプリケーションを張り直し
    ④レプリケーションがうまくいっていることを確認出来たら、サーバAのディストリビューション無効化実施
    ⑤時間はかかるがサーバAの肥大化が解消される。レプリケーションはサーバBにて稼働のまま。

    <懸念点>
    ④から⑤の間にディストリビューション無効化が行われますが、サーバBで稼働しているレプリケーション、
    および通常のSQL動作として問題がないでしょうか?

    →はい、これは通常のSQL操作です。 
    ただし、手順2では、ディストリビューターをサーバーBに設定する前に、サーバーA->レプリケーションを右クリック->ディストリビューターのプロパティ->パブリッシャーページ->パブリッシャータグのサーバーBのクリックを解除する必要があります。 
    次に、ディストリビューターをサーバーBに設定できます。 


    どうぞよろしくお願いいたします。

    MSDN/ TechNet Community Support Haruka
    ~参考になった投稿には「回答としてマーク」をご設定ください。なかった場合は「回答としてマークされていない」も設定できます。同じ問題で後から参照した方が、情報を見つけやすくなりますので、 ご協力くださいますようお願いいたします。また、MSDNサポートに賛辞や苦情がある場合は、MSDNFSF@microsoft.comまでお気軽にお問い合わせください。~

    2021年4月23日 7:45
    モデレータ
  • サーバ A の ディストリビュータ を使用したレプリケーションが存在しない状態になっているため、サーバ A で ディストリビューのみの無効化(削除) を実施したとしても、新たに作成したレプリケーションへの直接的な影響はないかと思います。

    なお、現状の状況時に ディストリビューのみの無効化(削除) を実施する場合にどの程度の負荷が発生するかは定かではありませんが、削除処理中に サーバ A に CPU、Disk I/O などの負荷が高くなるようであれば、サーバ A で実行されるアプリケーションからのクエリ処理が遅延したり、負荷に起因したレプリケーションの同期遅延などの間接的な影響はあるかもしれません。 

    2021年4月24日 14:20
  • 承知しました。

    ご回答ありがとうございます。

    2021年4月26日 1:31
  • hy.v13.21さん、こんにちは。フォーラムオペレーターのHarukaです。
    MSDNフォーラムにご投稿くださいましてありがとうございます。

    ご質問いただいた件ですが、その後いかがでしょうか。
    参考になった投稿があれば、 [回答としてマーク] をお願い致します。

    [回答としてマーク] 機能は設定された投稿が後から参照しやすくなりますので、
    同じ問題でお困りの方のためにも参考になった投稿に設定いただけますと幸いです。

    お手数ですが、ご協力の程どうかよろしくお願いいたします。

    MSDN/ TechNet Community Support Haruka
    ~参考になった投稿には「回答としてマーク」をご設定ください。なかった場合は「回答としてマークされていない」も設定できます。同じ問題で後から参照した方が、情報を見つけやすくなりますので、 ご協力くださいますようお願いいたします。また、MSDNサポートに賛辞や苦情がある場合は、MSDNFSF@microsoft.comまでお気軽にお問い合わせください。~

    2021年5月10日 2:07
    モデレータ
  • ご連絡が空いてしまいましたが、対応を行いました。

    ①サーバBのレプリケーションを一旦すべて削除

    ②サーバBへディストリビュータの設定を行う
    ③サーバBから、ディストリビュータBを使用してレプリケーションを張り直し
    ④レプリケーションがうまくいっていることを確認出来たら、サーバAのディストリビューション無効化実施
    ⑤時間はかかるがサーバAの肥大化が解消される。レプリケーションはサーバBにて稼働のまま。


    ①~③はうまくいきましたが、④の無効化実施時に20時間ほどかかった後、

    「distributionは使用中のため、削除できません。」

    が、表示されました。

    ここで疑問なのが、①の対応時に関連するレプリケーションはすべて消していて、新しいディストリビュータのレプリケーションだけになっていたはずです。

    上記エラーが表示されること自体が不思議なのですが、なにか理由はわかりますでしょうか?

    2021年8月4日 8:40
  • Distribution データベースへのアクセスされるものとしては、エージェント履歴のクリーンアップ、ディストリビューションのクリーンアップ などのエージェント ジョブがあるため、エージェント ジョブが起動していた可能性はあるかもしれません。
    2021年8月5日 10:47
  • ご回答ありがとうございます。

    エージェント履歴のクリーンアップ、ディストリビューションのクリーンアップは、どこかの削除のタイミングで消えていました。

    が、レプリケーションエージェントの検査は残っていて、それは稼働しているようでした。

    ※元々この作業が発生する起因になった内容と同じで、履歴はずっとエラーでしたが。

    このジョブを一旦無効化することで削除が可能になるでしょうか?

    見解をお聞かせいただけますと助かります。

    2021年8月6日 4:01
  • ディストリビューション データベースの削除に影響を及ぼすのかは定かではありませんが、レプリケーション エージェントの検査 ジョブでは、以下のストアドプロシージャがCALLされており、ディストリビューション データベースのオブジェクトへの参照などが行われていたため、本ジョブが実行されたことに起因し、ディストリビューション データベースの削除ができなかった可能性はあるかもしれません。

    そのため、レプリケーション関連ジョブが残っている場合は、無効化したうえで再度 ディストリビューション データベースの削除を実施してみると良いかもしれません。

    sys.sp_replication_agent_checkup -> sp_MSagent_retry_stethoscope
                                                      -> sp_MSagent_stethoscope

    SSMSからディストリビューション データベースを削除できない場合、以下のURLを参考にして削除を試してみると良いかと思います。

    ディストリビューターをドロップする
    https://docs.microsoft.com/ja-jp/troubleshoot/sql/replication/manually-remove-replication#drop-the-distributor

    2021年8月6日 16:49