Check Undistributed Commands

06 Jun 2022 23035 views 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.

		
			
T-SQL
			
Transact-SQL
			

			
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[CheckReplication_UndistributedCommands] @CmdCountThreshold INT = 100000
	,@SendMail BIT = 1
	,@Recipients VARCHAR(50) = 'dba@sqltreeo.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'
			

				

					

				

					
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
				
						
SET ANSI_NULLS ON
GO
 
SET QUOTED_IDENTIFIER ON
GO
 
ALTER PROCEDURE [dbo].[CheckReplication_UndistributedCommands] @CmdCountThreshold INT = 100000
	,@SendMail BIT = 1
	,@Recipients VARCHAR(50) = 'dba@sqltreeo.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'
					
				
			
		

' + 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'

		
			
T-SQL
			
Transact-SQL
			

			
';

			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;
			

				

					

				

					
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
				
						
';
 
			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;
					
				
			
		

You will also need the following function:

		
			
T-SQL
			
Transact-SQL
			

			
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- 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
			

				

					

				

					
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
				
						
SET ANSI_NULLS ON
GO
 
SET QUOTED_IDENTIFIER ON
GO
 
-- =============================================
-- 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