For these specifications:

  • Publisher: (prod OLTP DB server) SQL Server 2005, database size : 220 GB also acts as the distributor

  • Subscriber (Prod reporting DB server): SQL Server 2005, DB gets replicated and is currently in sync.

Now, as per the yearly activity there would be a script run on OLTP publisher DB to delete more than 3516009 records.

Please suggest if this breaks transaction replication? Or is there a way I can prevent one because setting that up is tedious?

Also, something similar was done 2 years back and we only have the error as

The process could not execute 'sp_replcmds' on 'xxxxxx'." "Status: 2, code: 0, text: 'Timeout expired'" "A time out occurred while waiting for memory resources to execute the query." "Agent 'xxx' is retrying after an error. 0 retries attempted. See agent job history in the Jobs folder for more details." "The step was cancelled (stopped) as the result of a stop job request""

And the fix that was done at that time was to increase the query time-out for log reader agent and was set from default of 1800 to a max 65333 secs.

Since the DB has grown over last 2 years we are wondering, if above fix will still be good or should we take any measures?

Also, in case replication breaks, what can be the fastest way to bring it back because setting the whole thing up (especially SQL Server 2005) will bring a big outage on reporting server?

1 Answer 1


Sounds like you were attempting to perform the delete operation in a single transaction. This being the case, the answer is "Yes, it will most likely break."

Even without replication, it's a bad idea to do such a large operation in a single batch. You would be better off looping a smaller number of deletes - it's faster and less of a performance issue. If you do this, the answer is, "No, it shouldn't be a problem."

Here's an example

Declare @keepGoing bit
set @keepGoing = 1

while @keepGoing =1

    if (select top 1 from someTable) is null
        set @keepGoing = 0
        delete sometable where ID in (select top 10000 ID from sometable)

  • thanks for the reply.. I have tested and found that if i delete in smaller chunks its taking more time than delete via one go. But the log file usage is saved. Since this being replication concern, how will deleting in chunks can help me. What i mean to say is what no of records in chunk will be better? Commented Jan 14, 2016 at 12:22
  • Also, the query takes less time to delete in chunks when i tested say twice or thrice may be cause of some part of plan may be in cache? Commented Jan 14, 2016 at 12:23
  • If your smaller deletes are still taking a long time, you will want to take a look at your indexing and make sure you are optimized for the query you are using to return your data set for deletion. Also make sure you aren't experiencing heavy fragmentation causing your reads/deletes to take longer. Yes, having run it more than once, will get the plan into cache, making subsequent executions faster. The other thing to look at is your process - why are you doing such a large delete? Is this something that can be handled more often to mitigate the volume? Commented Jan 14, 2016 at 15:41

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.