SQL Server – Will Transactional Replication Break with Heavy Update/Delete Queries?

replicationsql serversql-server-2005transactional-replication

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?

Best Answer

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
begin

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

end
Related Question