backupsql server
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%.
As you can see in this table, there is no conflict between Full backup and Log backup. They can occur at same time, and, when log backup completes, log will not be cleaned until full backup (or diff backup) completes.
Not all administrative tasks are allowed to run concurrently. In the table below, a black circle indicates two operations that cannot run in a database at the same time.
References:
Is the table too small?
Fragmentation figures are meaningless for small tables that span up only a few extents
This is captured by @minPageCount in the script. The default is 8: meaning it will be skipped if one extent in size.
Best Answer
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.
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.
That is, most likely, useless. Defrag is largely a myth. Stop Worrying About SQL Server Fragmentation