SQL Server: レプリケーションでログリーダーが動作していることを確認するクエリ群と再起動の手順

たまにログリーダーがハングしたりするので、状況確認のためのクエリをまとめておきます。

ログリーダーが稼働していることの確認①

use distribution

select count(*) from dbo.MSrepl_commands with(nolock)

push/pull関係なく、パブリッシャ側で実行する。ログリーダーが読み取ったレプリ対象コマンドを保存するテーブルなので、数秒～数分ごとに値が変化しているはず。

ここが例えば10分くらい変化しない場合はログリーダーがハングしているか、データ更新が一切されていない可能性がある。

ログリーダーが稼働していることの確認②

select top 10 last_request_end_time, * from sys.dm_exec_sessions where program_name like '%logreader%'

パブリッシャ側の任意のDBで実行。ログリーダーに関するセッションが2つあるはず。

last_request_end_timeが、最後にログリーダーが稼働した日時。片方は数分単位でしか稼働しないが、片方は数秒単位でどんどん稼働日時が更新されていくはず。

このクエリを実行したときに、last_request_end_timeが2行とも10分以上前だったら、ログリーダーがハングしている可能性を疑う。

パフォーマンスカウンタでの確認

select top 1000 * from sys.dm_os_performance_counters
where object_name like '%replication%'
and counter_name like '%cmds%'
--and instance_name in ('***')

パブリッシャ側で確認する。cntr_valueの値が変化していればレプリ動いている。正常に動いている場合でも、このカウ「プロセスが実行中で、サーバーからの応答を待機しています。」のメッセージのまま。ンタは数分に1回程度のカウントアップ頻度の場合があった。そのため、補助的な情報として確認するくらいでいいかも。

ログリーダーエージェントのプログラムが稼働していることの確認

DBサーバーにログインし、タスクマネージャーから「legread.exe」が「実行中」であることを確認。
ログリーダーエージェントを停止した場合は、タスクマネージャーからも消える。

実際にログリーダーがハングしている時の挙動

-- 直近 6 時間の情報を取得
DECLARE @targetTime datetime
SET @targetTime = DATEADD(mm, -6, GETDATE())
 
SELECT
    dh.time,
    da.id,
    da.local_job,
    da.publication,
    pd.publisher_db,
    da.subscriber_db,
    da.name,
    da.subscriber_name,
    CASE dh.runstatus
        WHEN 1 THEN '開始'
        WHEN 2 THEN '成功'
        WHEN 3 THEN '実行中'
        WHEN 4 THEN 'アイドル'
        WHEN 5 THEN '再試行'
        WHEN 6 THEN '失敗'
    END AS runstatus,
    rt.entry_time,
    dh.start_time,
    dh.comments,
    dh.duration,
    dh.current_delivery_rate,
    dh.current_delivery_latency,
    dh.delivered_commands,
    dh.average_commands,
    dh.delivery_latency,
    dh.total_delivered_commands,
    dh.xact_seqno,
    dh.error_id
FROM
    distribution.dbo.MSrepl_transactions AS rt
    LEFT JOIN
    distribution.dbo.MSdistribution_history AS dh
    ON
    dh.xact_seqno = rt.xact_seqno
    LEFT JOIN
    distribution.dbo.MSpublisher_databases AS pd
    ON
    pd.id = rt.publisher_database_id
    LEFT JOIN
    distribution.dbo.MSdistribution_agents AS da
    ON
    da.id = dh.agent_id
WHERE
    dh.time IS NOT NULL
    AND
    dh.time >= @targetTime
ORDER BY
    dh.time DESC

※SEの雑記の小澤さんに作成いただきました。

クエリの実行結果が「プロセスが実行中で、サーバーからの応答を待機しています。」のメッセージのまま。

手動でログリーダーエージェントを再起動するときのポイント

ハングしていると判断した場合は、ログリーダーの再起動が必要な場合があります。
その手順と、気をつけるポイントをまとめておきます。

０．以下のクエリをパブリッシャ側のサーバーのどのDBからでも良いので実行。2レコードとれることを確認

select top 10 * from sys.dm_exec_sessions where program_name like '%logreader%'

１．エージェントを停止

２．すぐに再開せずに、以下のクエリ再度実行。0レコードになることを確認。

select top 10 * from sys.dm_exec_sessions where program_name like '%logreader%'

→このとき、一時的に1レコードだけ取得できる（＝すぐには停止しない）場合がある。

その場合は1-2分待って再度クエリを実行すると、0レコードになる（＝完全停止）

３．エージェントを再開

再開後、一時的にログリーダーが活発に稼働することでレプリエージェントまわりでブロッキングが起きるケースがよくあるが、
このブロッキングに関しては待っていれば自然解消されるため問題ない。