Long Latency on Scubscriber in Transaction Replication(SQL SERVE R2008 R2)

Question

Hi everyone,

We have a large database(SQL SERVE R2008 R2),which is highly being hited.For read operation,we have created a push transactional replication .We have dedicated a seperated machine for Distributor and some days we face with a long latency(2-3 hours) in Subsciber.When I checked on subscriber,I noticed we have lots of locking on Tempdb(These locks are being generated because of different reports,that are running against subscriber database).Is it possible these blocks are causing latency on Subscriber?Also,Does subscriber use the tempdb?

Thanks

---

A.G

Answer 1

 

Refer to:
http://www.mssqltips.com/sqlservertip/2590/determine-data-latency-between-publisher-and-subscriber-in-sql-server-transactional-replication/.
http://msdn.microsoft.com/en-us/library/ms151178(v=sql.105).aspx.

Answer 2

Yes, it is highly probably there locks are being held during the report generation process which is causing an increase in latency in replication. If possible use the no-lock hint on the reports to minimize the contention between the reporting process and replication.

---

looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941