SQL Server Replication Queries
Useful replication queries to monitor and debug issues
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.dbo.MSreplication_monitordata
WHERE publisher_db = 'your_publisher_db'
AND publication IN ('ALL','your_publication_name')
ORDER BY publisher,
agent_type,
publication
The MSreplication_monitordata table in distribution database contains cached data used by Replication Monitor. So you will not get the real time details on latency, performance, etc.
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
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
Undistributed commands for each table
If you want to find undelivered commands for each table in a publication, you can get the detail from MSdistribution_status view from distribution database.
SELECT
p.name as publisher_server,
da.publisher_db,
s.name as subscriber_server,
da.subscriber_db,
da.publication as publication,
a.destination_object as table_name,
ds.DelivCmdsInDistDB as delivered_commands_in_distribution_db,
ds.UndelivCmdsInDistDB as undelivered_commands_in_distribution_db
FROM distribution.dbo.MSdistribution_status ds
INNER JOIN distribution.dbo.MSdistribution_agents da ON da.id = ds.agent_id
INNER JOIN distribution.dbo.MSArticles a ON a.publisher_id = da.publisher_id AND a.publisher_db = da.publisher_db AND a.article_id = ds.article_id
INNER JOIN master.sys.servers s ON s.server_id = da.subscriber_id
INNER JOIN master.sys.servers p ON p.server_id = da.publisher_id
WHERE p.name = 'your_publisher_server_name'
AND s.name = 'your_subscriber_server_name'
AND da.publisher_db = 'your_publisher_db_name'
AND da.subscriber_db = 'your_subscriber_db_name'
AND ds.UndelivCmdsInDistDB <> 0
ORDER BY undelivered_commands_in_distribution_db DESC,
table_name ASC
Find replication errors in error log.
EXEC sys.sp_readerrorlog 0,1,'Replication','-'
| Parameter | Value |
| Parameter 1 | 0 = Current error log, 1 = Previous error log |
| Parameter 2 | 1 = SQL Server, 2 = SQL Server Agent |
| Parameter 3 | Search keyword to filter the log |
| Parameter 4 | Additional Search keyword to filter the log |
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
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
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
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'
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
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;
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
Did you find this article valuable?
Support Rajanand Ilangovan by becoming a sponsor. Any amount is appreciated!