2

What are the performance drawbacks when running an Index Defrag during a Full Database Backup? Is there a way to pause/stop an Index Defrag when a backup starts?

I utilize Reorganize when frag is between 10 and 30%, I utilize Rebuild when frag is greater than 30%.

3
  • 1
    CPU and Disk will experience higher strain. What method of index defragmentation are you using? If something like Ola Hallengren's scripts, then I would imagine that you could edit the defragmenter to check before issuing an defragment command if a backup is in progress and to wait. Jan 15, 2016 at 17:07
  • Why would you ever be running these two things at the same time? Jan 15, 2016 at 17:35
  • I don't ever but just because I fixed this issue from happening...I have 192+ SQL Servers split between 2 DBAs and I have a separate server team that handles backups with CommVault. This is a precautionary measure in case wires are crossed between maintenance times with Server / DBA teams. Jan 15, 2016 at 22:33

2 Answers 2

3

What are the performance drawbacks when running an Index Defrag during a Full Database Backup?

Any database operation will generate extra log that the full backup needs to capture (the Full backup contains, at least, all log denerated during the backup). However, defrag is fairly small log overhead. I would not be worried.

Is there a way to pause/stop an Index Defrag when a backup starts?

No, but Defrag is perfectly OK to stop and resume later. If you interrupt defrag you won't loose the work so far. Restarting a defrag later will continue from where it was interrupted.

I utilize Reorganize when frag is between 10 and 30%,

That is, most likely, useless. Defrag is largely a myth. Stop Worrying About SQL Server Fragmentation

0

There is no way of pausing a running defrag on an index. Having said that, you can always pause BETWEEN defrags as you probably have more than one object to defrag.

If you are using the wizard for handling your defrags, you are quite out of luck, however.

I always find it better to use dedicated scripts (like Ola Hallengren's scripts) to defragment. The Maintenance Wizard never seems to get it done in a good way. Having a dedicated script easily lets you insert 'pause' logic based on different criteria.

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.