- Docs
- /
06 Jun 2022 25587 views 0 minutes to read Contributors
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.
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.
123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182 SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOALTER PROCEDURE [dbo].[CheckReplication_UndistributedCommands] @CmdCountThreshold INT = 100000,@SendMail BIT = 1,@Recipients VARCHAR(50) = 'dba@sqltreeo.com',@Subscribers VARCHAR(1000) = NULL,@CopyRecipients VARCHAR(50) = NULLASBEGINSET NOCOUNT ON;IF (DB_ID('distribution') IS NULL)RETURNDECLARE @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 @tSELECT 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.idFROM distribution.dbo.MSdistribution_agents da WITH (NOLOCK)JOIN master.sys.servers publisher WITH (NOLOCK) ON publisher.server_id = da.publisher_idJOIN master.sys.servers subscriber WITH (NOLOCK) ON subscriber.server_id = da.subscriber_idAND (@Subscribers IS NULLOR charindex(subscriber.[name], @Subscribers) > 0)JOIN distribution.dbo.MSpublications p WITH (NOLOCK) ON p.publication = da.publicationAND p.publisher_id = da.publisher_idAND p.publisher_db = da.publisher_dbJOIN distribution.dbo.MSsubscriptions s WITH (NOLOCK) ON s.publication_id = p.publication_idAND 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 1FROM @t)BEGINSELECT 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_idFROM @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_idAND runstatus IN (2,3,4)GROUP BY start_time)SELECT @lastrunts = max(TIMESTAMP)FROM dist_sessions;SELECT @xact_seqno = xact_seqno,@avg_rate = delivery_rateFROM distribution.dbo.MSdistribution_history WITH (NOLOCK)WHERE agent_id = @agent_idAND TIMESTAMP = @lastrunts;SELECT @xact_seqno = isnull(@xact_seqno, 0x0),@avg_rate = isnull(@avg_rate, 0.0);IF (@avg_rate = 0.0)BEGINSELECT @avg_rate = isnull(avg(delivery_rate), 0.0)FROM distribution.dbo.MSdistribution_historyWHERE 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 @tresSET publisher = @publisher,publisher_db = @publisher_db,publication = @publication,subscriber = @subscriber,subscriber_db = @subscriber_db,estimatedprocesstime = CASEWHEN @avg_rate != 0.0THEN CAST((CAST(pendingcmdcount AS FLOAT) / @avg_rate) AS INT)ELSE pendingcmdcountENDWHERE publication IS NULL;DELETEFROM @tWHERE @publisher = publisherAND @publisher_db = publisher_dbAND @publication = publicationAND @subscriber = subscriberAND @subscriber_db = subscriber_dbAND @subscription_type = subscription_type;END;IF EXISTS (SELECT 1FROM @tresWHERE pendingcmdcount >= @CmdCountThreshold)BEGINIF @SendMail = 1BEGINDECLARE @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'
Publisher | Publisher DB | Publication | Subscriber | Subscriber DB | Pending CMD | Estimated Process Time |
---|
1234567891011121314151617181920212223242526272829303132333435363738 ';EXEC msdb.dbo.sp_send_dbmail @recipients = @Recipients,@copy_recipients = @CopyRecipients,@subject = @subject,@importance = @importance,@body = @tableHTML,@body_format = 'HTML';ENDELSEBEGINSELECT 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 estimatedprocesstimeFROM @tresWHERE pendingcmdcount >= @CmdCountThresholdORDER BY 1,2,3,4,5;-- [BEGIN]-FOR NAGIOSDECLARE @cmdcount INT;SELECT @cmdcount = MAX(pendingcmdcount)FROM @tres;RETURN @cmdcount;-- [END]-FOR NAGIOSEND;END;END;
You will also need the following function:
12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849505152535455565758596061626364656667686970717273 SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO-- =============================================-- 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)ASBEGINDECLARE @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 daysIF @remaining_sec > 86400 -- 1 dayBEGINSET @nbr_days = @remaining_sec / 86400;SET @remaining_sec = @remaining_sec - (@nbr_days * 86400);END;ELSESET @nbr_days = 0;-- number of hoursIF @remaining_sec > 3600BEGINSET @nbr_hours = @remaining_sec / 3600;SET @remaining_sec = @remaining_sec - (@nbr_hours * 3600);END;ELSESET @nbr_hours = 0;-- number of minutesIF @remaining_sec > 60BEGINSET @nbr_mins = @remaining_sec / 60;SET @remaining_sec = @remaining_sec - (@nbr_mins * 60);END;ELSESET @nbr_mins = 0;-- number of secondsSET @nbr_sec = @remaining_sec;SET @remaining_sec = @remaining_sec - @nbr_sec;SET @outputstr = CASEWHEN @nbr_days = 0THEN ''ELSE CAST(@nbr_days AS VARCHAR) + 'd 'END + CASEWHEN @nbr_hours = 0THEN ''ELSE CAST(@nbr_hours AS VARCHAR) + 'h 'END + CASEWHEN @nbr_mins = 0THEN ''ELSE CAST(@nbr_mins AS VARCHAR) + 'm 'END + CAST(@nbr_sec AS VARCHAR) + 's ';RETURN @outputstr;END;GO