Finding Undistributed commands in Transactional replication

-> Below query can be used to find undistributed command count in transactional replication.

set nocount on
declare @publisher varchar(255)
declare @publisher_db varchar(255)
declare @Subscriber varchar(255)
declare @Subscriber_db varchar(255)
declare @subscription_type int
declare @subscriber_id int

set @publisher = ‘<Publisher SQL Instance Name>’
set @publisher_db = ‘<Publisher Database>’
set @Subscriber = ‘<Subscriber SQL Instance Name>’
set @subscriber_db = ‘<Subscriber Database>’

set @subscriber_id = (select distinct b.subscriber_id from master.dbo.sysservers a INNER JOIN MSsubscriptions b on a.srvid = b.subscriber_id where a.srvname=@Subscriber)

Declare @PublicationName VARCHAR(1000)
DECLARE CheckUndistributedcmdinbadway_cursor CURSOR FOR

select distinct b.publication,a.subscription_type from MSsubscriptions a inner join MSpublications b on a.publication_id = b.publication_id where a.subscriber_id = @subscriber_id

OPEN CheckUndistributedcmdinbadway_cursor
FETCH NEXT FROM CheckUndistributedcmdinbadway_cursor INTO @PublicationName,@subscription_type
WHILE @@FETCH_STATUS = 0
BEGIN
print ‘Publication: ‘+@PublicationName
EXECUTE sp_replmonitorsubscriptionpendingcmds
@publisher = @publisher,
@publisher_db = @publisher_db,
@publication =@PublicationName,
@subscriber =@Subscriber,
@subscriber_db =@subscriber_db,
@subscription_type =@subscription_type
print ‘————————————————————————‘
print ‘ ‘
FETCH NEXT FROM CheckUndistributedcmdinbadway_cursor INTO @PublicationName,@subscription_type
END
CLOSE CheckUndistributedcmdinbadway_cursor
DEALLOCATE CheckUndistributedcmdinbadway_cursor

-> Before running this query, select “Results to Text (CTRL + T)” from SQL Server management studio and execute it.

-> The query is not the best way to get the undistributed command count in transactional replication. But helped me solve my purpose. Can be useful for someone else.

-> The query will be blocked if distribution cleanup is running.

Thank You,
Vivek Janakiraman

Disclaimer:
The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s