I have a VLDB that is being replicated using transnational replication. The performance has been terrible, and the process frequently times out.

After much investigation, including Service Packs and hotfixes, page file increase, TempDB tweaks, RAM increases, SAN tests, even a new node in the cluster(!) we have finally found that the issue is being caused by a Database Snapshot on the subscriber.

I have seen a similar issue with index rebuilds, where the Ghost clean-up task was blocking the Copy-On-Write thread, and the resolution was to disable the Ghost clean-up task.

Unfortunately, in this case, disabling the ghost clean-up task has not resolved the issue, and replication continues to get Query Time-outs, usually during delete operations. Increasing the query timeout period simply increases the amount of time before it times-out!

Has anybody come across this, or have any ideas as to how to resolve the issue? Removing the Database Snapshot, even during the period of replication is not possible, as it is supporting user reports.

  • when you say replication times out usually during delete operations are you replicating Deletes to subscriber/s or is it when you are generating a Snapshot to be applied to subscriber/s (not the database snapshot, the snapshot that replication generates when you reinitialize or add new articles, etc) ?
    – Kin Shah
    Commented Jun 26, 2013 at 14:24
  • 1
    @Kin - it is when deletes are being transitionally replicated, not during the snapshot initialization. However, snapshot erplication is 8x faster without the snapshot Commented Jun 27, 2013 at 22:03
  • Do you have a requirement to propagate Delete's to the subscribers ? Have you thought of Using Custom Stored Procedures for Inserts, Updates, and Deletes at Subscribers [msdn.microsoft.com/en-us/library/aa237133(v=sql.80).aspx]
    – Kin Shah
    Commented Jun 27, 2013 at 22:21
  • @kin - Unfortunately this is not "politically" possible. Also, I an not convinced that it would help, as the issue is not with getting the data over the wire, it is actually the high volume IO vs the database snapshot. Commented Jun 28, 2013 at 8:02
  • 1
    Have you looked into wait stats analysis ? This is very unusaual to me. Possibly not a replication issue, but something else is the root cause.
    – Kin Shah
    Commented Jun 28, 2013 at 12:59


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.