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)