sql-lover (9/19/2012)
PaulB-TheOneAndOnly (9/19/2012)
Any chance of doing it during a maintenance window where you set recovery mode to simple? that would keep t-logs from growing.Hi Paul,
1st, thanks for reply.
I'm afraid I am not following you (or did not understand your statement)
The recovery model will not keep it under control if it's an open transaction; the ReIndex job will fail anyway if there is no space to growth. Moreover, I would not change a recovery model to SIMPLE if not needed.
However, it is in SIMPLE right now 🙂 ... as we do not use FULL at work (business reasons)
Any other suggestion of how to calculate that?
A "Maintenance Window" is a chunck of time where only DBA have access to the database - you lock users out and stop SQL Server Agent therefore there are no transactions in the system other than the ones DBA generates.
Then, you execute your reindex commands one at a time or in a restricted number of threads; being recovery model set to simple transaction log space will be released (made available back to the engine) after each index gets rebuilt.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.