Replication - victim of deadlock

[RonFusionHSLLC]

We are doing transaction replication of a 2012 SS to a 2019 SS. 

From time to time, index rebuild's are run on the source. 

The rebuild task takes around 20mins, during that time we have seen Replication become the victim in a deadlock.

What is the consensus on re-indexing source tables while replication is running.

rm

[Michael_Litz]

When there is a known maintenance window - like the re-org we recommend that you stop the task before and then resume the task after the maintenance.

Michael

[PGN]

When you're doing maintenance, why is there any replication going on?  Generally when you run maint, the system shouldn't be processing any transactions.

[lyka · Accepted solution]

Hello,

Please refer to this article:

https://community.qlik.com/t5/Knowledge/SQL-Server-Source-Rebuild-Index-Best-Practices-and/ta-p/1890...

Hope this helps!

Thanks

Lyka

[Heinvandenheuvel]

All good advice, but dollars to donuts this is simply due to the attrep_truncation_safeguard table which always has open transactions, one for each active task, two rows per task. It does not need a rebuild ever.

Just exclude all attrep% tables from the rebuild process  and be happy?

Let us know if this helps?

Hein.

[RonFusionHSLLC]

Yep, we are doing all the dumb things...

Rebuilding during the highest production load times, replicating at the same time.  Using DBCC DBREINDEX on a 2012 database...I'm sure we are doing even more dumb stuff, like assuming everything that just works on our Oracle database works on our SS databases.

Read the article linked above...complete eye opener.

New plan...

Semaphore index re - build/organize and backups with replication startup/shutdown (use the cmdline commands start/stop)

rebuild once a week or so (just not every day)

reorg daily, with lower fill factors

Oh yeah, and don't do any of this during the busiest processing times.

To Hein's point...yep, we see a ton of blocking reported on the attrep_truncation_safeguard, that table definitely needs to be taken out of the re - build/organize list.

Thanks for the good discussion.

rm 

[lyka]

Hi Ron,

If any of the solutions worked for you, please mark as solution accepted.

Thanks

Lyka

[RonFusionHSLLC]

Just to cover off on this topic...

First off - thanks for the responses, extremely valuable advice

We now realize that there is no way to run Replicate during index rebuilds on a Standard Addition SQLServer without conflicts.  That might not be the case with Enterprise, but we don't have Enterprise until we move to Azure later this year.

We opted to use the built in scheduler to stop Replicate and start Replicate for a period of time so indexing and backups can run on the source SQLServer without conflicts.  That works, however 'starting' the replication task from the scheduler seems to cause a full reload every time.

I'm calling stop/start the solution. 

Now I need to move on to finding the solution for full reload vs. just start up where you left off.  I'm guessing the command line interface is the first place to look, as the GUI scheduler (Replicate webpage) doesn't seem to have any options.  If I manually stop/resume processing, I don't get full reloads; just picks up where it left off, so I find it odd the scheduler 'start' doesn't have the option to resume.

Our source endpoint setup is set to 'Prioritize Online Logs' (replicate does not currently have access to backup log files - next part of the project)

Ron

[lyka]

Hi Ron,

The "run task" option should let you do a resume.

From user guide: Run task to run or resume the task(s) at the scheduled time.

https://help.qlik.com/en-US/replicate/November2021/Content/Global_Common/Content/SharedEMReplicate/S...

If that is not the case, please open a support case

Thanks

Lyka

[RonFusionHSLLC]

I see it...

The dropdown should say Run/Resume  - it simply says Run. 

I had Reload chosen, so I got exactly what I asked for.

Apparently - run/resume = same thing as 'Run Task' in the scheduler. 

That might make a huge difference overall

Thanks again

ron