delivering replicated transactions no update on subscriber--Replication seems stuck( SOLVED)

 delivering replicated transactions 

1. I have a translation replication and it runs fine all day, all of a sudden the distribution job says 

 delivering replicated transactions 

2, at 8am when the business starts customer agent /accounting team say data is not updated on the application. ( data gets from another server)

This happens when data is not being updated on the subscriber.

next:

1. I looked at the distribution job which was running for almost 12 hours and no data us being updated and it is holding X lock on Subscriber, not blocking anything or no performance issues except data is not being updated.

I see there are so many indexes on Subscriber matching publisher and thought that could be the reason the update is taking ever

Note: you will have to only keep the indexes u need, find unused and clean them frequently

so i decided to stop the distribution job, drop indexes and start the distribution job -did not work.

tried MSDN, google a lot of suggestions.

btw- make sure replication cleanup jobs run off-hours and they could be sometimes blocking a while cleaning replicates tables ..these also did not help for me.

after 2 days of struggle, i found that daily at 6am there are BULK GP transactions are being posted on the publisher and that is taking ever to get replicated, so I decided to start a snapshot agent( reinitialize subscription or invalidate the snapshot ) at 7am after GP posting is done and then all looks green..

 

--1.daily 07:15am;

---re intiallize subscription by invaliding the snapshot:job

use DBNAME

go

exec sp_reinitsubscription

@publication = PUBLICATIONANME', 

@subscriber = 'SUBSCRIBER'---all,

,@destination_db ='DBNAME',

@invalidate_snapshot =1

go

---O/P: Invalidated the existing snapshot of the publication. Run the Snapshot Agent again to generate a new snapshot.

--RUn below to START SNAPSHOT JOB  

USE msdb ;  

GO    

EXEC dbo.sp_start_job N'SNAPSHOT AGENT JOB' ;  

GO 

 --MONITOR SNAPSHOT AND DISTRIBUTION JOB: BOTH SHOULD RUN, WAIT FOR 5 MINUTES IF NOT START DISTRIBUTION JOB:

 ---IF worked PLAN TO AUTOMATE ..until the tables data moves to history 

check snapshot agent repl data folder 

also, check the count of rows using except

IF NOT EXISTS (

select count(*) from DBname.dbo.Table with (nolock)---20574581

EXCEPT

select count(*) from Subscriber server. dbname..dbo.table with (nolock)---20574581

)

if matches or no send an email ...

immediatesnapshot=1, allowanonymus=1: don't change :::

also, check indexes on the subscriber( in my case 1 was only useful)

 

Cannot construct data type date, some of the arguments have values which are not valid.

 drop table test 

go

create table test 

(ExpDate  varchar(20))

insert into test values 

('06/22'), ('01/22'), ('02/22'), ('02/20'), ('01/20')

;with cte as (

Select DATEFROMPARTS(2000 + CAST(right(ExpDate,2) AS INT), CAST(left(ExpDate,2) AS INT), 1) AS ExpDate

from test 

)

SELECT ExpDate

from cte

WHERE(ExpDate) BETWEEN DATEADD(MM,DATEDIFF(MM,0,GETDATE()),0) AND DATEADD(MM,DATEDIFF(MM,0,GETDATE())+1,0)

/*

ExpDate

----------

2020-02-01

2020-01-01

*/

insert into test values ('13/22')

;with cte as (

Select DATEFROMPARTS(2000 + CAST(right(ExpDate,2) AS INT), CAST(left(ExpDate,2) AS INT), 1) AS ExpDate

from test 

)

SELECT ExpDate

from cte

WHERE(ExpDate) BETWEEN DATEADD(MM,DATEDIFF(MM,0,GETDATE()),0) AND DATEADD(MM,DATEDIFF(MM,0,GETDATE())+1,0)

/*

ExpDate

----------

2020-02-01

2020-01-01

Msg 289, Level 16, State 1, Line 22

Cannot construct data type date, some of the arguments have values which are not valid.

*/

--using CTE tables  and DATEFROMPARTS: did not work 

--Cannot construct data type date, some of the arguments have values which are not valid.

-----USE TEMP TABLES INSTED OF CTE ,