Check for Undistributed Commands

20 Aug 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.

CREATE PROCEDURE [dbo].[CheckReplication_UndistributedCommands]
@CmdCountThreshold INT = 100000
,@SendMail BIT = 1
,@Recipients VARCHAR(50) = 'youremail@domain.com'
,@Subscribers VARCHAR(1000) = NULL
,@CopyRecipients VARCHAR(50) = NULL
AS
BEGIN

SET NOCOUNT ON;

IF( DB_ID('distribution') IS NULL )
RETURN

DECLARE
@publisher SYSNAME
,@publisher_id INT
,@publisher_db SYSNAME
,@publication SYSNAME
,@subscriber SYSNAME
,@subscriber_id INT
,@subscriber_db SYSNAME
,@subscription_type INT
,@agent_id INT
,@lastrunts TIMESTAMP
,@avg_rate FLOAT
,@xact_seqno VARBINARY(16)
,@cmd NVARCHAR(500);

DECLARE @t TABLE (
publisher SYSNAME
,publisher_id INT
,publisher_db SYSNAME
,publication SYSNAME
,subscriber SYSNAME
,subscriber_id INT
,subscriber_db SYSNAME
,subscription_type INT
,agent_id INT);

INSERT @t
SELECT DISTINCT
publisher.name publisher
,da.publisher_id
,p.publisher_db
,p.publication
,subscriber.name subscriber
,da.subscriber_id
,s.publisher_db
,da.subscription_type
,da.id
FROM distribution.dbo.MSdistribution_agents da with (nolock)
JOIN master.sys.servers publisher with (nolock)
ON publisher.server_id = da.publisher_id
JOIN master.sys.servers subscriber with (nolock)
ON subscriber.server_id = da.subscriber_id
AND
(
@Subscribers IS NULL
OR charindex(subscriber.[name], @Subscribers) > 0
)
JOIN distribution.dbo.MSpublications p with (nolock)
ON p.publication = da.publication
AND p.publisher_id = da.publisher_id
AND p.publisher_db = da.publisher_db
JOIN distribution.dbo.MSsubscriptions s with (nolock)
ON s.publication_id = p.publication_id
AND s.publisher_id = da.publisher_id;

DECLARE @tres TABLE (
publisher SYSNAME NULL
,publisher_db SYSNAME NULL
,publication SYSNAME NULL
,subscriber SYSNAME NULL
,subscriber_db SYSNAME NULL
,pendingcmdcount INT
,estimatedprocesstime INT);

WHILE EXISTS(select 1 from @t)
BEGIN
SELECT TOP 1
@publisher = publisher
,@publisher_id = publisher_id
,@publisher_db = publisher_db
,@publication = publication
,@subscriber = subscriber
,@subscriber_id = subscriber_id
,@subscriber_db = subscriber_db
,@subscription_type = subscription_type
,@agent_id = agent_id
FROM @t;

;WITH dist_sessions (start_time, runstatus, timestamp)
AS( SELECT
start_time
,max(runstatus)
,max(timestamp)
FROM distribution.dbo.MSdistribution_history with (nolock)
WHERE agent_id = @agent_id
AND runstatus in (2,3,4)
GROUP BY start_time)
SELECT
@lastrunts = max(timestamp)
FROM dist_sessions;

SELECT
@xact_seqno = xact_seqno
,@avg_rate = delivery_rate
FROM distribution.dbo.MSdistribution_history with (nolock)
WHERE agent_id = @agent_id
AND timestamp = @lastrunts;

SELECT @xact_seqno = isnull(@xact_seqno, 0x0), @avg_rate = isnull(@avg_rate, 0.0);

IF (@avg_rate = 0.0)
BEGIN
SELECT @avg_rate = isnull(avg(delivery_rate),0.0)
FROM distribution.dbo.MSdistribution_history
WHERE agent_id = @agent_id;
END;

SET @cmd = N'distribution.sys.sp_MSget_repl_commands
@agent_id = @agent_id
,@last_xact_seqno = @xact_seqno
,@get_count = 2
,@compatibility_level = 9000000';

INSERT @tres(pendingcmdcount)
EXEC sp_executesql
@cmd
,N'@agent_id int, @xact_seqno varbinary(16)'
,@agent_id = @agent_id, @xact_seqno = @xact_seqno;

UPDATE @tres
SET
publisher = @publisher
,publisher_db = @publisher_db
,publication = @publication
,subscriber = @subscriber
,subscriber_db = @subscriber_db
,estimatedprocesstime = CASE WHEN @avg_rate != 0.0
THEN CAST((CAST(pendingcmdcount as float) / @avg_rate) as int)
ELSE pendingcmdcount
END
WHERE publication IS NULL;

DELETE FROM @t
WHERE @publisher = publisher
AND @publisher_db = publisher_db
AND @publication = publication
AND @subscriber = subscriber
AND @subscriber_db = subscriber_db
AND @subscription_type = subscription_type;
END;

IF EXISTS(SELECT 1 FROM @tres WHERE pendingcmdcount >= @CmdCountThreshold)
BEGIN
IF @SendMail = 1
BEGIN
DECLARE
@subject VARCHAR(100) = 'Replication Latency (Undistributed Commands)'
,@body VARCHAR(1000)
,@computername VARCHAR(128) = @@SERVERNAME
,@tableHTML NVARCHAR(MAX)
,@importance VARCHAR(10);

SELECT
@subject = @computername + ' ' + @subject
,@importance='High';

SET @tableHTML =
N'<table border="1">' +
N'<tr>' +
N'<th>Publisher</th>' +
N'<th>Publisher DB</th>' +
N'<th>Publication</th>' +
N'<th>Subscriber</th>' +
N'<th>Subscriber DB</th>' +
N'<th>Pending CMD</th>' +
N'<th>Estimated Process Time</th>' +
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'</tr></table>';

EXEC msdb.dbo.sp_send_dbmail
@recipients = @Recipients
,@copy_recipients = @CopyRecipients
,@subject = @subject
,@importance = @importance
,@body = @tableHTML
,@body_format = 'HTML';
END
ELSE
BEGIN
SELECT
publisher
,publisher_db
,publication
,subscriber
,subscriber_db
,pendingcmdcount
-- ,CAST(DATEPART(DAY, DATEADD(SECOND, estimatedprocesstime, '19000101'))-1 AS VARCHAR) + FORMAT(DATEADD(SECOND, estimatedprocesstime, '19000101'), '.HH:mm:ss') estimatedprocesstime
,dbo.fn_timediff_formatted('1900-01-01', estimatedprocesstime) AS estimatedprocesstime
FROM @tres
WHERE pendingcmdcount >= @CmdCountThreshold
ORDER BY 1, 2, 3, 4, 5;

-- [BEGIN]-FOR NAGIOS
DECLARE @cmdcount INT;

SELECT @cmdcount = MAX(pendingcmdcount)
FROM @tres;

RETURN @cmdcount;
-- [END]-FOR NAGIOS
END;
END;
END;
GO

 

You will also need the following function:

/****** Object: UserDefinedFunction [dbo].[fn_timediff_formatted] Script Date: 17-06-2019 16:01:59 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author: kde
-- Create date: 12/04/2019
-- Description: Displays time difference between two dates (in random order) in a formatted string
-- Sample call:
-- SELECT dbo.[fn_timediff_formatted](CURRENT_TIMESTAMP, CURRENT_TIMESTAMP-4.33)
-- =============================================
CREATE FUNCTION [dbo].[fn_timediff_formatted]
(
@d1 DATETIME,
@d2 DATETIME
)
RETURNS VARCHAR(50)
AS
BEGIN

DECLARE
@outputstr VARCHAR(50) = '',
@nbr_days INT,
@nbr_hours INT,
@nbr_mins INT,
@nbr_sec INT;

DECLARE @remaining_sec INT = ABS(DATEDIFF(SECOND, @d1, @d2)); -- order of @d1 and @d2 doesn't matter

-- number of days
IF @remaining_sec > 86400 -- 1 day
BEGIN
SET @nbr_days = @remaining_sec / 86400;
SET @remaining_sec = @remaining_sec - (@nbr_days * 86400);
END;
ELSE
SET @nbr_days = 0;

-- number of hours
IF @remaining_sec > 3600
BEGIN
SET @nbr_hours = @remaining_sec / 3600;
SET @remaining_sec = @remaining_sec - (@nbr_hours * 3600);
END;
ELSE
SET @nbr_hours = 0;

-- number of minutes
IF @remaining_sec > 60
BEGIN
SET @nbr_mins = @remaining_sec / 60;
SET @remaining_sec = @remaining_sec - (@nbr_mins * 60);
END;
ELSE
SET @nbr_mins = 0;

-- number of seconds
SET @nbr_sec = @remaining_sec;
SET @remaining_sec = @remaining_sec - @nbr_sec;

SET @outputstr = CASE
WHEN @nbr_days = 0
THEN
''
ELSE
CAST(@nbr_days AS VARCHAR) + 'd '
END + CASE
WHEN @nbr_hours = 0
THEN
''
ELSE
CAST(@nbr_hours AS VARCHAR) + 'h '
END + CASE
WHEN @nbr_mins = 0
THEN
''
ELSE
CAST(@nbr_mins AS VARCHAR) + 'm '
END + CAST(@nbr_sec AS VARCHAR) + 's ';

RETURN @outputstr;

END;
GO

 

 

In this article