Number of non-distributed commands in SQL Server monitoring publications

Original: SQL Server monitors the number of commands that are not distributed in the publication

We usually look at the number of commands that are not distributed.

However, when the server has a lot of releases, it is troublesome to open the view

Of course, if you want to use a script to see the more convenient, run the following statement

--View the number of commands not distributed by each publication and estimated time select  ' EXEC distribution.sys.sp_replmonitorsubscriptionpendingcmds @publisher = N '        + A.publisher + ", @publisher_db = n" + a.publisher_db        + ", @publication = n" "+ A.publication +", @sub Scriber = N '        + c.name + ', @subscriber_db = n ' + b.subscriber_db        + ', @subscription_type = ' + CAST (b.subsc Ription_type as VARCHAR) from    distribution.dbo.MSreplication_monitordata A (NOLOCK) INNER JOIN (SELECT   publication_id, subscriber_id, subscriber_db, Subscription_typefrom     distribution.dbo.MSsubscriptions (NOLOCK) GROUP by publication_id, subscriber_id, subscriber_db, subscription_type) b on a.publication_id = B.publication_idinner J OIN sys.servers C (NOLOCK) on b.subscriber_id = C.server_idwhere   A.agent_type = 1

the above automatically generates stored procedures for each published command that is not distributed, and executes to see the commands and estimated time that each publication waits for distribution:

EXEC Distribution.sys.sp_replmonitorsubscriptionpendingcmds @publisher = n ' publisher ', @publisher_db = n ' Publish database ', @ Publication = n ' Publish name ', @subscriber = n ' subscriber ', @subscriber_db = n ' subscription database ', @subscription_type =0

/***************************************************************************//********************************* ******************************************/

However, because of the monitoring needs, just want to count all the published non-distributed commands "Pendingcmdcount" the sum,

Originally wanted to use the following method to insert the return value of each stored procedure into the temporary table, but there is no such method!!

CREATE TABLE #countab (pendingcmdcount int,estimatedprocesstime int) INSERT into #countab (Pendingcmdcount, Estimatedprocesstime) EXEC Distribution.sys.sp_replmonitorsubscriptionpendingcmds @publisher = N ' publisher ', @publisher_ db = n ' publication database ', @publication = n ' publication name ', @subscriber = n ' subscriber ', @subscriber_db = n ' subscription database ', @subscription_type =0

The above method is also feasible, but only local tests are feasible. is to create a linked server and add it to the database distribution front.

The actual server has a local linked server, but it doesn't show up, and it doesn't work. Although you can create other named, you do not want to create too many linked servers.

Then thought of changing the system's stored procedures!! ~ It's wrong. The reference system stored procedure creates a stored procedure that is almost as defined.

That is, you do not need DISTRIBUTION.SYS.SP_REPLMONITORSUBSCRIPTIONPENDINGCMDS to create a similar dbo.sp_getsubscriptionpendingcmds, as follows

CREATE PROCEDURE Dbo.sp_getsubscriptionpendingcmds (@publisher sysname--cannot be null, @publisher_db Sysna Me--cannot be null, @publication sysname--cannot is null, @subscriber sysname--cannot be null, @subsc riber_db sysname--cannot be null, @subscription_type int, @pendingcmdcount int output) as Begin set Noco  UNT on declare @retcode int, @agent_id int, @publisher_id int, @subscriber_id int, @lastrunts timestamp, @xact_seqno varbinary, @inactive int = 1, @virtual int = 1 Select @publisher_id = server_id from sys.servers where upper          (name) = UPPER (@publisher) Select @subscriber_id = server_id from sys.servers where upper (name) = Upper (@subscriber)           Select @agent_id = ID from distribution.dbo.MSdistribution_agents where publisher_id = @publisher_id and publisher_db = @publisher_db and publication in (@publication, ' all ') and subscriber_id = @ subscriber_id And subscriber_db = @subscriber_db and subscription_type = @subscription_type; with Dist_sessions (start_time , Runstatus, timestamp) as (select Start_time, max (Runstatus), Max (timestamp) from distribution.db  o.msdistribution_history where agent_id = @agent_id GROUP by start_time) Select @lastrunts =          Max (timestamp) from dist_sessions where Runstatus in (2,3,4); if (@lastrunts is null) BEGIN if exists (SELECT * from Distribution.dbo.MSpublications p INNER join Distribu Tion.dbo.MSsubscriptions s on p.publication_id = s.publication_id where p.publisher_id = @publisher_id and P.publisher_ db = @publisher_db and p.publication = @publication and P.immediate_sync = 1 and s.status = @inactive and S.subscriber_                  id = @virtual) begin SELECT ' Pendingcmdcount ' = 0, N ' estimatedprocesstime ' = 0 return 0 End Select @lastrunts = max (timestamp) from Distribution.dbo.MSdistribution_hiStory where agent_id = @agent_id End Select @xact_seqno = xact_seqno from Distribution.dbo.MSdistribution_histor Y where agent_id = @agent_id and timestamp = @lastrunts Select @xact_seqno = IsNull (@xact_seqno, 0x0) DECLARE @ Countab TABLE (pendingcmdcount int) insert INTO @countab (pendingcmdcount) exec @retcode = Distribution.sys . sp_msget_repl_commands @agent_id = @agent_id, @last_xact_seqno = @xact_seqno, @get_count = 2, @compatibility_level = 9000000 if (@retcode! = 0 or @ @error! = 0) Return 1 Select @pendingcmdcount =pend   Ingcmdcount from @countab return @pendingcmdcountend

The above stored procedure mainly changes 3 places and then runs in one of the other databases, and the page can be run in distribution.

1. One more return parameter, @pendingcmdcount int output, returns the number of non-distributed commands

2. Estimated time not required, removed

3. Query each table or internal stored procedure plus the database prefix distribution

Then use a stored procedure to add up all the values to the counter!

Create a new job, schedule every minute, and observe performance counter statistics.

This can also be cacti captured again! ~

SQL Server monitors the number of commands that are not distributed in a publication