November 10, 2010 at 6:58 am
I received complaints about stale data on some reports. I checked our replication from our production database to our reporting server database. By using the built in replication monitor, I could see that the log reader agent was running and I could even see that commands were being issued. However, on several tables, I could run comparison queries between the production and reporting databases and the data had stopped being replicated (in this case the data was roughly four hours old).
I reinitialized the publication and the data is current again. Up to that point the reports were always near-real-time with very little latency. I'm at a loss; the only thing I can even think of is if a log reader agent profile options are set to low or something. Can anyone give me a clue?
In the meantime, I'm scheduling re-initializations nightly just to be safe.
Publisher/Distributor is MSSQL 2005
Subscriber is MSSQL 2008
Thanks,
James
November 10, 2010 at 11:55 am
are you getting any 14151 errors in the app log and pop ups on the console. you have to check the actual console and not RDP.
and have you checked the individual subscription jobs? what versions of SQL are you running? on some of the older builds there was a bug where if you run alter index with online = on it would mess up replication. could also be db corruption
January 23, 2011 at 8:51 pm
James,
Sometimes the subscribers goes out of sync due to which we have to re-initialize it. In most cases due to some issues with the n/w it takes a long time for the data to reach the subscriber.Check the Replication Monitor and have a look at the Latency Period. If latency is high you can have a word with the member of the NOC team to confirm whether there is any issue with the N/W through which the data is flowing.
Hope this helps.
Satnam
January 24, 2011 at 7:56 am
i've had really good luck with managing indexes on the subscribers. i run the index usage DMV's and delete any unused indexes. it has helped increase the reliability of replication since there is much less wasted I/O.
a lot of our publications we used to reinitialize on a regular basis every few weeks. but lately most of them haven't been touched in months. they just run and run
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply