Cacher is the code snippet organizer for pro developers

 -- http://sqlblog.com/blogs/maria_zakourdaev/archive/2012/03/06/transactional-replication-are-you-sure-your-data-is-totally-synchronized.aspx
 -- http://sqlmag.com/sql-server/validating-replicated-data
 
 use  paes_accent
 exec sp_publication_validation 
 @publication = 'billing',
 @rowcount_only = 1, 
 @full_or_fast = 1

 -- read the log for errors
 exec sp_readerrorlog 0,1,'failed data validation'

USE GreenMountain
GO
sp_publication_validation @publication =  'Billing'  
      , @rowcount_only = 2
      , @full_or_fast = 2
GO

USE GreenMountainMarket
GO
sp_publication_validation @publication =  'Market'  
      , @rowcount_only = 2
      , @full_or_fast = 2
GO

USE distribution
go
SELECT * FROM MSrepl_errors WHERE error_text LIKE '%out of sync%' AND time>'2016-08-14 16:00:00' ORDER BY time DESC
GO

use distribution
go
select * from MSrepl_errors 
where error_text like '%row was not found%'
order by time desc
go

exec sp_browsereplcmds '0x002F70A00001EC45000B00000000', '0x002F70A00001EC45000B00000000'


delete from distribution.dbo.MSrepl_commands where article_id in (21)



with h as
	(
	SELECT   agent_id ,
							'maxseq' = ISNULL(MAX(xact_seqno), 0x0)
				   FROM     Distribution.dbo.MSdistribution_history WITH ( NOLOCK )
				   GROUP BY agent_id
	)
	SELECT  pubs.name as publisher_name,
			pub.publication as Publication_name,
			s.publisher_db,
			a.name AS agent_name,
			subs.name as subscriber_name,
			s.subscriber_db,
			x.source_owner + '.' + x.source_object as table_name, t.article_id,
			'undelivered_commands' = count(*)	
	FROM    Distribution.dbo.MSrepl_commands t WITH (NOLOCK) 		
			JOIN Distribution.dbo.MSsubscriptions s WITH (NOLOCK) ON (t.article_id = s.article_id AND t.publisher_database_id = s.publisher_database_id)
			inner join master.sys.servers subs with(nolock) on subs.server_id = s.subscriber_id
			inner join master.sys.servers pubs with(nolock) on pubs.server_id=s.publisher_id
			join Distribution.dbo.MSpublications pub with(nolock) on (pub.publication_id=s.publication_id)
			JOIN h ON (h.agent_id = s.agent_id)
			INNER JOIN Distribution.dbo.MSdistribution_agents a with(nolock) ON a.id = s.agent_id AND a.publisher_database_id = s.[publisher_database_id]
			INNER JOIN distribution.dbo.MSarticles x on t.article_id=x.article_id and s.publication_id=x.publication_id and x.publisher_id=s.publisher_id
	where t.xact_seqno > h.maxseq and
	a.subscriber_ID >= 0
	GROUP BY pubs.name,pub.publication,s.publisher_db,a.name, subs.name,s.subscriber_db,x.source_owner + '.' + x.source_object , t.article_id

use [LibertyMarket]
exec sp_dropsubscription @publication = N'Market', @subscriber = N'AWISUSEPCS01', @destination_db = N'db21Market_Replication', @article = N'all'
GO

DECLARE @publicationDB AS sysname;
DECLARE @publication AS sysname;
SET @publicationDB = N'LibertyMarket'; 
SET @publication = N'Market'; 

-- Remove a transactional publication.
USE [LibertyMarket]
EXEC sp_droppublication @publication = @publication, @ignore_distributor = 1

sp_removedbreplication @dbname = N'LibertyMarket'

-- Remove replication objects from the database.
USE [master]
EXEC sp_replicationdboption 
  @dbname = 'LibertyMarket', 
  @optname = N'publish', 
  @value = N'false';
GO

SELECT   
				@@SERVERNAME		AS [distributor_server]
			, p.[publication]	AS [publication_name]
			, p.[publication_type]
			, srv.[srvname]		AS [publisher_server]
			, p.[publisher_db]
			, ss.[srvname]		AS [subscriber_server]
			, s.[subscriber_db] 
			, s.[status]		AS [subscription_status]
			, s.[subscription_type]
			, COUNT(DISTINCT s.[article_id]) AS [subscription_articles]
	FROM distribution..MSpublications p 
	JOIN distribution..MSsubscriptions s ON p.[publication_id] = s.[publication_id] 
	JOIN master..sysservers ss ON s.[subscriber_id] = ss.[srvid]
	JOIN master..sysservers srv ON srv.[srvid] = p.[publisher_id]
	JOIN distribution..MSdistribution_agents da ON da.[publisher_id] = p.[publisher_id] AND da.[subscriber_id] = s.[subscriber_id] 
Where s.status = 0
	GROUP BY p.[publication]
			, srv.[srvname]
			, p.[publisher_db]
			, ss.[srvname]
			, s.[subscriber_db] 
			, s.[status]
			, p.[publication_type]
			, s.[subscription_type]

/*
	2016 - Dan Andrei STEFAN (danandrei.stefan@aurea.com)

	execute on Aurea/Ista PCS client server (start of replication chain)
	will update the replication publication between PCS26 and PRO-BISQLRPT-01 (Accent/IGS)
	the rest of the chain should not be affected by replication changes, but only by data changes
*/
USE Billing_Replication
GO

/* check publication */
EXEC sp_helppublication
/*
	immediate_sync: should be false
	allow_annonymous: should be false
*/
/*
	EXEC sp_changepublication   @publication = 'Billing', 
								@property = 'allow_anonymous' , 
								@value = 'false' 
	GO 
	EXEC sp_changepublication	@publication = 'Billing', 
								@property = 'immediate_sync' , 
								@value = 'false' 
GO 
*/
GO

/* check subscription */
EXEC sp_helpsubscription
GO

/* add missing articles to existing publication */
exec sp_addarticle	  @publication = N'Billing'
					, @article = N'PaymentSchedule'
					, @source_owner = N'dbo'
					, @source_object = N'PaymentSchedule'
					, @type = N'logbased'
					, @description = N''
					, @creation_script = N''
					, @pre_creation_cmd = N'drop'
					, @schema_option = 0x000000000803509F
					, @force_invalidate_snapshot = 0
					, @identityrangemanagementoption = N'manual'
					, @destination_table = N'PaymentSchedule'
					, @destination_owner = N'dbo'
					, @status = 24
					, @vertical_partition = N'false'
					, @ins_cmd = N'CALL [sp_MSins_dboPaymentSchedule]'
					, @del_cmd = N'CALL [sp_MSdel_dboPaymentSchedule]'
					, @upd_cmd = N'SCALL [sp_MSupd_dboPaymentSchedule]'
GO

exec sp_addarticle	  @publication = N'Billing'
					, @article = N'PaymentScheduleDetail'
					, @source_owner = N'dbo'
					, @source_object = N'PaymentScheduleDetail'
					, @type = N'logbased'
					, @description = N''
					, @creation_script = N''
					, @pre_creation_cmd = N'drop'
					, @schema_option = 0x000000000803509F
					, @force_invalidate_snapshot = 0
					, @identityrangemanagementoption = N'manual'
					, @destination_table = N'PaymentScheduleDetail'
					, @destination_owner = N'dbo'
					, @status = 24
					, @vertical_partition = N'false'
					, @ins_cmd = N'CALL [sp_MSins_dboPaymentScheduleDetail]'
					, @del_cmd = N'CALL [sp_MSdel_dboPaymentScheduleDetail]'
					, @upd_cmd = N'SCALL [sp_MSupd_dboPaymentScheduleDetail]'
GO

EXEC sp_refreshsubscriptions @publication = N'Billing'
GO

/*
	start snapshot agent: 2 articles will be included in the snapshot
*/

-- https://blogs.msdn.microsoft.com/chrissk/2010/01/03/using-sp_repldone-to-mark-all-pending-transactions-as-having-been-replicated/
-- https://www.mssqltips.com/sqlservertip/3288/sql-server-replication-error--the-specified-lsn-for-repldone-log-scan-occurs-before-the-current-start-of-replication-in-the-log/
REPLICATION LOG READER IS STUCK
Because of an update that affected 20 million rows, the logreader times out after 5 minutes of scanning the log

The solution: apply the changes manually to subscriber.
Before applying the changes to subscriber, you have to mark the transactions as replicated

-- check transaction replication information
USE saes_spark
dbcc opentran

-- display unreplicated transactions
select  [Transaction ID], count([Transaction ID]) as 'Count log entrie'
from::fn_dblog('354124:11520:1', null)   --  Oldest non-distributed LSN : (55:4803:2) 
where [Description] = 'REPLICATE'
group by [Transaction ID] 
GO

–Show transactions pending in the Transaction Log 
sp_replshowcmds

–Mark all transactions as having been replicated.
EXEC sp_repldone @xactid = NULL, @xact_segno = NULL, @numtrans = 0, @time = 0, @reset = 1

–Re-run sp_replshowcmds, no pending transactions.
sp_replshowcmds

-- check to see if there are not active transactions
DBCC OPENTRAN
 
-–Flush Transaction Log article cache
sp_replflush