Rajanand Ilangovan
Rajanand Ilangovan

Rajanand Ilangovan

SQL Server Replication Queries

Useful replication queries to monitor and debug issues

Rajanand Ilangovan's photo
Rajanand Ilangovan
·May 10, 2022·

7 min read

Subscribe to my newsletter and never miss my upcoming articles

Listen to this article

Table of contents

Replication

Replication Monitor

SELECT publisher,
publisher_db,
publication_id,
CASE publication_type
    WHEN 0 then '0 - Transactional publication'
    WHEN 1 then '1 - Snapshot publication'
    WHEN 2 then '2 - Merge publication'
END AS publication_type_desc,
publication,
CASE agent_type
    WHEN 1 then '1 - Snapshot Agent'
    WHEN 2 then '2 - Log Reader Agent'
    WHEN 3 then '3 - Distribution Agent'
    WHEN 4 then '4 - Merge Agent'
    WHEN 9 then '9 - Queue Reader Agent'
END AS agent_type,
agent_name,
CASE status
    WHEN 1 THEN '1 - Started'
    WHEN 2 THEN '2 - Succeeded'
    WHEN 3 THEN '3 - In progress'
    WHEN 4 THEN '4 - Idle'
    WHEN 5 THEN '5 - Retrying'
    WHEN 6 THEN '6 - Failed'
END AS agent_status,
RIGHT('0' + CAST(cur_latency / 3600 AS VARCHAR),2) + ':' +
RIGHT('0' + CAST((cur_latency / 60) % 60 AS VARCHAR),2) + ':' +
RIGHT('0' + CAST(cur_latency % 60 AS VARCHAR),2) AS cur_latency,
RIGHT('0' + CAST(worst_latency / 3600 AS VARCHAR),2) + ':' +
RIGHT('0' + CAST((worst_latency / 60) % 60 AS VARCHAR),2) + ':' +
RIGHT('0' + CAST(worst_latency % 60 AS VARCHAR),2) AS max_latency,
RIGHT('0' + CAST(best_latency / 3600 AS VARCHAR),2) + ':' +
RIGHT('0' + CAST((best_latency / 60) % 60 AS VARCHAR),2) + ':' +
RIGHT('0' + CAST(best_latency % 60 AS VARCHAR),2) AS min_latency,
RIGHT('0' + CAST(avg_latency / 3600 AS VARCHAR),2) + ':' +
RIGHT('0' + CAST((avg_latency / 60) % 60 AS VARCHAR),2) + ':' +
RIGHT('0' + CAST(avg_latency % 60 AS VARCHAR),2) AS avg_latency,
last_distsync AS last_time_dist_agent_run,
isagentrunningnow AS is_agent_running_now, 
agentstoptime AS agent_stop_time,
CASE warning
    WHEN 1 THEN 'Expiration'
    WHEN 2 THEN 'Latency'
    WHEN 4 THEN 'Merge expiration '
    WHEN 16 THEN 'Merge slow run duration '
    WHEN 32 THEN 'Merge fast run speed '
    WHEN 64 THEN 'Merge slow run speed'
END AS warning,
CASE retention_period_unit
    WHEN 1 THEN CAST(retention AS VARCHAR)+' Week'
    WHEN 2 THEN CAST(retention AS VARCHAR)+' Month'
    WHEN 3 THEN CAST(retention AS VARCHAR)+' Year'
END AS pub_retention_period,
distdb AS distribution_db
FROM distribution..MSreplication_monitordata
WHERE publisher_db = 'your_publisher_db'
AND publication IN ('ALL','your_publication_name')
ORDER BY publisher, 
agent_type, 
publication

image.png

Replication alerts

SELECT A.alert_id,
A.error_id,
A.time AS alert_time,
E.time AS error_time,
A.publisher,
A.publisher_db,
CASE A.publication_type 
    WHEN 0 THEN 'Snapshot'
    WHEN 1 THEN 'Transactional'
    WHEN 2 THEN 'Merge'
END AS publication_type_desc,
A.publication as publication_name,
A.subscriber,
A.subscriber_db,
A.article,
A.source_object,
A.destination_object,
E.error_text,
A.alert_error_text,
A.agent_id,
CASE A.agent_type 
    WHEN 1 THEN 'Snapshot Agent'
    WHEN 2 THEN 'Log Reader Agent'
    WHEN 3 THEN 'Distribution Agent'
    WHEN 4 THEN 'Merge Agent'
    ELSE 'Unknown'
END AS agent_type_desc,
COALESCE(S.name,L.name,D.name,M.name) AS agent_name,
E.session_id AS agent_session_id,
CASE status 
    WHEN 0 THEN 'Unserviced'
    WHEN 1 THEN 'serviced'
END AS status_desc
FROM msdb.dbo.sysreplicationalerts AS A
LEFT JOIN distribution.dbo.MSrepl_errors AS E ON A.error_id = E.id
LEFT JOIN distribution.dbo.MSsnapshot_agents AS S ON S.id = A.agent_id
LEFT JOIN distribution.dbo.MSlogreader_agents AS L ON L.id = A.agent_id
LEFT JOIN distribution.dbo.MSdistribution_agents AS D ON D.id = A.agent_id
LEFT JOIN distribution.dbo.MSmerge_agents AS M ON M.id = A.agent_id
WHERE A.time >= GETUTCDATE()-1
AND A.publisher_db = 'your_publisher_db_name'
AND A.publication = 'your_publication_name'
AND A.subscriber_db = 'your_subscriber_db_name'
ORDER BY A.alert_id DESC

image.png

Undistributed commands

The number of pending commands that are yet to be applied to the subscriber by distribution agent for a transactional publication. It also provides a rough estimate of how much time it takes to process them.

EXEC distribution.dbo.sp_replmonitorsubscriptionpendingcmds 
    @publisher = 'your_publisher_server_name', 
    @publisher_db = 'your_publisher_db_name', 
    @publication = N'your_publication_name',
    @subscriber = 'your_subscriber_server_name', 
    @subscriber_db = 'your_subscriber_db_name',
    @subscription_type = 1 -- 0=push subscription and 1=pull

image.png

Find replication errors in error log.

EXEC sys.sp_readerrorlog 0,1,'Replication','-'
ParameterValue
Parameter 10 = Current error log, 1 = Previous error log
Parameter 21 = SQL Server, 2 = SQL Server Agent
Parameter 3Search keyword to filter the log
Parameter 4Additional Search keyword to filter the log

image.png

Agent History

Get details about snapshot agent history

SELECT H.time AS message_log_time,
H.agent_id,
A.name AS agent_name,
H.runstatus,
CASE runstatus 
    WHEN 1 THEN 'Start'
    WHEN 2 THEN 'Succeed'
    WHEN 3 THEN 'In progress'
    WHEN 4 THEN 'Idle'
    WHEN 5 THEN 'Retry'
    WHEN 6 THEN 'Fail'
END AS runstatus_desc,
H.start_time AS job_exec_start_time,
H.duration AS session_duration_in_sec,
H.comments AS message_text,
H.delivered_transactions AS transactions_delivered_in_session,
H.delivered_commands AS delivered_commands_per_sec,
H.delivery_rate AS delivery_rate_per_sec,
A.publisher_db,
A.publication,
H.error_id,
E.time AS error_time,
E.error_code,
E.error_text
FROM distribution.dbo.MSsnapshot_history AS H
INNER JOIN distribution.dbo.MSsnapshot_agents AS A ON H.agent_id = A.id
LEFT JOIN distribution.dbo.MSrepl_errors AS E ON E.id = H.error_id
WHERE H.time >= GETUTCDATE()-7
AND A.publisher_db = 'your_publisher_db_name'
ORDER BY message_log_time desc

image.png

Get details about log reader agent history

SELECT H.time AS message_log_time,
H.agent_id,
A.name AS agent_name,
H.runstatus,
CASE runstatus 
    WHEN 1 THEN 'Start'
    WHEN 2 THEN 'Succeed'
    WHEN 3 THEN 'In progress'
    WHEN 4 THEN 'Idle'
    WHEN 5 THEN 'Retry'
    WHEN 6 THEN 'Fail'
END AS runstatus_desc,
H.start_time AS job_exec_start_time,
H.duration AS session_duration_in_sec,
H.comments AS message_text,
H.delivered_transactions AS transactions_delivered_in_session,
H.delivered_commands AS delivered_commands_per_sec,
H.delivery_rate AS delivery_rate_per_sec,
A.publisher_db,
A.publication,
H.error_id,
E.time AS error_time,
E.error_code,
E.error_text
FROM distribution.dbo.MSlogreader_history AS H
INNER JOIN distribution.dbo.MSlogreader_agents AS A ON H.agent_id = A.id
LEFT JOIN distribution.dbo.MSrepl_errors AS E ON E.id = H.error_id
WHERE H.time >= GETUTCDATE()-7
AND A.publisher_db = 'your_publisher_db_name'
ORDER BY message_log_time desc

image.png

Get details about distribution agent history

SELECT H.time AS message_log_time,
H.agent_id,
A.name AS agent_name,
H.runstatus,
CASE runstatus 
    WHEN 1 THEN 'Start'
    WHEN 2 THEN 'Succeed'
    WHEN 3 THEN 'In progress'
    WHEN 4 THEN 'Idle'
    WHEN 5 THEN 'Retry'
    WHEN 6 THEN 'Fail'
END AS runstatus_desc,
H.start_time AS job_exec_start_time,
H.duration AS session_duration_in_sec,
H.comments AS message_text,
H.current_delivery_rate AS current_delivery_rate_per_sec,
H.current_delivery_latency/1000  AS current_delivery_latency_in_sec,
H.delivery_rate AS delivery_rate_per_sec,
H.delivery_latency/1000 AS delivery_latency_in_sec,
H.delivered_transactions AS transactions_delivered_in_session,
H.delivered_commands AS commands_delivered_in_session,
H.average_commands AS average_commands_in_session,
H.total_delivered_commands, --total commands delivered since subscription was created
A.publisher_db,
A.publication,
A.subscriber_db,
H.error_id,
E.time AS error_time,
E.error_code,
E.error_text
FROM distribution.dbo.MSdistribution_history AS H
INNER JOIN distribution.dbo.MSdistribution_agents AS A ON H.agent_id = A.id
LEFT JOIN distribution.dbo.MSrepl_errors AS E ON E.id = H.error_id
WHERE H.time >= GETUTCDATE()-2
AND A.publisher_db = 'your_publisher_db_name'
AND A.publication = 'your_publiication_name'
AND A.subscriber_db = 'your_subscriber_db_name'
ORDER BY message_log_time desc

image.png

Agents Detail

Get details about snapshot agents

SELECT *, 
CASE publication_type 
    WHEN 0 THEN 'Transactional'
    WHEN 1 THEN 'Snapshot'
    WHEN 2 THEN 'Merge'
END AS publication_type_desc,
CASE publisher_security_mode 
    WHEN 0 THEN 'Microsoft SQL Server Authentication'
    WHEN 1 THEN 'Microsoft Windows Authentication'
END AS publisher_security_mode_desc
FROM distribution.dbo.MSsnapshot_agents
WHERE publisher_db = 'your_publisher_db_name'
AND publication = 'your_publication_name'
ORDER BY id

Get details about log reader agents

SELECT *, 
CASE publisher_security_mode 
    WHEN 0 THEN 'Microsoft SQL Server Authentication'
    WHEN 1 THEN 'Microsoft Windows Authentication'
END AS publisher_security_mode_desc
FROM distribution.dbo.MSlogreader_agents
WHERE publisher_db = 'your_publisher_db_name'
AND publication = 'your_publication_name'
ORDER BY id

Get details about distribution agents

SELECT * ,
CASE subscription_type 
    WHEN 0 THEN 'Push'
    WHEN 1 THEN 'Pull'
    WHEN 2 THEN 'Anonymous'
END AS subscription_type_desc
FROM distribution.dbo.MSdistribution_agents
WHERE publisher_db = 'your_publisher_db_name'
AND publication = 'your_publication_name'
AND subscriber_db = 'your_subscriber_db_name'
ORDER BY id

Get details about merge agents

SELECT *, 
CASE publisher_security_mode 
    WHEN 0 THEN 'Microsoft SQL Server Authentication'
    WHEN 1 THEN 'Microsoft Windows Authentication'
END AS publisher_security_mode_desc,
CASE subscriber_security_mode 
    WHEN 0 THEN 'Microsoft SQL Server Authentication'
    WHEN 1 THEN 'Microsoft Windows Authentication'
END AS subscriber_security_mode_desc
FROM distribution.dbo.MSmerge_agents
WHERE publisher_db = 'your_publisher_db_name'
AND publication = 'your_publication_name'
ORDER BY id

Get details about Q-reader agents

SELECT * 
FROM distribution.dbo.MSqreader_agents

Get details about the agent profiles

SELECT profile_id,
profile_name,
agent_type,
CASE agent_type 
    WHEN 1 THEN 'Snapshot Agent'
    WHEN 2 THEN 'Log Reader Agent'
    WHEN 3 THEN 'Distribution Agent'
    WHEN 4 THEN 'Merge Agent'
    WHEN 9 THEN 'Queue Reader Agent'
END AS agent_type_desc,
type AS profile_type,
CASE type 
    WHEN 0 THEN 'System'
    WHEN 1 THEN 'Custom'
END AS profile_type_desc,
def_profile AS is_default_profile
FROM msdb.dbo.MSagent_profiles
ORDER BY agent_type

image.png

Publisher Detail

Get the publisher database details

SELECT id, 
publisher_id, 
publisher_db, 
publisher_engine_edition, 
CASE publisher_engine_edition 
    WHEN 10 THEN 'Personal Edition' 
    WHEN 11 THEN 'Desktop Engine (MSDE)' 
    WHEN 20 THEN 'Standard' 
    WHEN 21 THEN 'Workgroup' 
    WHEN 30 THEN 'Enterprise (Evaluation)' 
    WHEN 31 THEN 'Developer' 
    ELSE 'Express'
END AS publisher_engine_edition_name
FROM distribution.dbo.MSpublisher_databases
ORDER BY id

Subscriber Detail

Get details about subscriber

-- Execute in Publisher Server
EXEC sp_helpsubscriberinfo;

Get subscription details

USE your_publisher_db;
EXEC sp_helpsubscription;

image.png

Get subscriber schedule details


SELECT 
publisher,
subscriber,
CASE agent_type 
    WHEN 0 THEN '0 = Distribution Agent'
    WHEN 1 THEN '1 = Merge Agent'
END AS agent_type,
frequency_interval,
CASE frequency_type 
    WHEN 1 THEN '1 = One time.'
    WHEN 2 THEN '2 = On demand.'
    WHEN 4 THEN '4 = Daily.'
    WHEN 8 THEN '8 = Weekly.'
    WHEN 16 THEN '16 = Monthly.'
    WHEN 32 THEN '32 = Monthly relative.'
    WHEN 64 THEN '64 = Autostart.'
    WHEN 128 THEN '128 = Recurring.'
END AS frequency_type,
--frequency_relative_interval,
--frequency_recurrence_factor,
frequency_subday_interval,
CASE frequency_subday 
    WHEN 1 THEN '1 = Once.'
    WHEN 2 THEN '2 = Second.'
    WHEN 4 THEN '4 = Minute.'
    WHEN 8 THEN '8 = Hour.'
END AS frequency_subday,
active_start_date,
active_start_time_of_day,
active_end_date,
active_end_time_of_day 
FROM distribution.dbo.MSsubscriber_schedule

image.png

Did you find this article valuable?

Support Rajanand Ilangovan by becoming a sponsor. Any amount is appreciated!

See recent sponsors Learn more about Hashnode Sponsors