Check for Undistributed Commands

28 Sep 2019 0 minutes to read Contributors

Situation

When you have replication implemented you need to check for problems in delivering data to the subscribers. In case the data cannot be delivered to a subscriber due to many different reasons, the undistributed commands will accumulate. 

Checking for the amount of undistributed commands, gives you a heads up on that.

Solution

Below procedure and function can be implemented on the distribution server. You can schedule this procedure in a job every five minutes and it will send out a mail informing you of the situation.

' + N'' + N'' + N'' + N'' + N'' + N'' + N'' + N'' + isnull(CAST(( SELECT [td] = publisher ,'' ,[td] = publisher_db ,'' ,[td] = publication ,'' ,[td] = subscriber ,'' ,[td] = subscriber_db ,'' ,[td] = pendingcmdcount ,'' -- ,[td] = CAST(DATEPART(DAY, DATEADD(SECOND, estimatedprocesstime, '19000101'))-1 AS VARCHAR) + FORMAT(DATEADD(SECOND, estimatedprocesstime, '19000101'), '.HH:mm:ss') ,[td] = dbo.fn_timediff_formatted('1900-01-01', estimatedprocesstime) FROM @tres WHERE pendingcmdcount >= @CmdCountThreshold ORDER BY 1 ,2 ,3 ,4 ,5 FOR XML PATH('tr') ,TYPE ) AS NVARCHAR(MAX)), '') + N'

Publisher Publisher DB Publication Subscriber Subscriber DB Pending CMD Estimated Process Time

You will also need the following function:

In this article