We have multiple machines where we have pre allocated the size of the transaction log to 50gb. The size of the table that I am trying to reorganize is 55 – 60 gb but is going to continuously increase. The main reason I want to reorganize is to reclaim space and any performance benefit because of that is an added bonus.
The fragmentation level of the table is 30 – 35%. On some of these machines I get "transaction log full" error and reorganize fails. The transaction log size reaches upto 48gb. What is a good way to counter this? We do not have auto increment switched on and I am reluctant to do so.
I can increase the log size to a larger value but as the table size increases in the future, the value may not be enough. Also it defeats the purpose of doing reorganize to reclaim space if I am going to increase the log size equally. Any ideas on how I can effectively counter this? Using bulked mode is not an option since data loss is not acceptable.
Best Answer
Best practice is to
REORGANIZEbelow around 30% fragmentation andREBUILDabove this. Simply,REBUILDmakes a clean copy,REORGANIZEdoes it in-situ.Check what you're actually doing: you don't have a maintenance plan doing both do you?
On larger tables (50GB table is getting there) I've seen
REORGANIZEconsume all transaction log space if you follow this rule. Not often: only one system with a certain load pattern. TheREORGANIZEjust ran until the log expanded and consumed all disk space.We switched to
REBUILDinstead with no more problems, but ignored fragmentation below 25%. This worked better for us: you'll have to see if it works for you.REBUILDmay affect the performance more thanREORGANIZEin production, but this can sometimes be mitigated with theONLINEoption (Enterprise Edition required).