Skip to main content

​
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.
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

​
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
image.png
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'
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
—

​
How to regenerate the sp_MS custom stored procedures in SQL Server

If you are adding a subscription using “initialize with LSN” option when the log reader agent is stopped, the auto generated stored procs will not be generated. You can use the below script to generate it. This script will generate the below stored procedures for each of the table article that are replicated.
  • sp_MSins*
  • sp_MSupd*
  • sp_MSdel*
USE your_publisher_db;
exec sp_scriptpublicationcustomprocs @publication = 'your_publication_name'
Once you execute, you will get the stored procedure definitions. You can just copy that and execute it in subscriber database to create the stored procedures. The generated stored procedure’s schema is based on the publisher database’s schema. Sample Output:
image.png
⌘I