May 12, 2014

Microsoft SQL Server Merge Replication Client Status Information

Below is a select statement which can be placed in a view, for providing information on core merge replication status information usually seen using Microsoft SQL Server Replication Monitor GUI tool.

SELECT TOP 1000 [subscriber_server]
,SubStatus =
CASE [status]
WHEN 0 THEN ‘Inactive’
WHEN 1 THEN ‘Active’
WHEN 2 THEN ‘Deleted’
,convert(nvarchar(100), [last_sync_date], 100) as LastSyncedOn
,LastSyncStatus =
CASE [last_sync_status]
WHEN 0 THEN ‘All jobs are waiting to start’
WHEN 1 THEN ‘One or more jobs are starting’
WHEN 2 THEN ‘All jobs have executed successfully’
WHEN 3 THEN ‘At least one job is executing’
WHEN 4 THEN ‘All jobs are scheduled and idle’
WHEN 5 THEN ‘At least one job is attempting to execute after a previous failure’
WHEN 6 THEN ‘At least one job has failed to execute successfully’
,ReplicationVersion =
CASE [replica_version]
WHEN 90 THEN ‘SQL Server 2005’
WHEN 100 THEN ‘SQL Server 2008’

FROM [MyDatabase].[dbo].[sysmergesubscriptions]
where subscriber_type=2
order by [last_sync_date] asc

Below might be needed as well for non-sysadmins

grant select on [sysmergesubscriptions] to public

More details on the system table used can be found by clicking here.

Leave a Reply