September 19, 2012 at 12:03 pm
1st, apologies if this has been asked before. I remember I faced this issue few years ago, but do not remember exactly what my calculations were.
Basically, I have a medium size database (over 100GB) that needs a reindex job for 1st time. I know for sure, it will hit space hard that 1st time, but I do have some space constraints so I may run out of space before the job completes.
How can I calculate the amount of space required for the Tlog so the job won't fail? I am currently calculating that with top 5 tables and adding a 10 to 20 percentage to that. Is that correct? I know the best way to know this is after the job runs, but that's not an option here. So I am looking for a ballpark number based on some math and educated guess.
Any other comment or suggestion is welcome.
September 19, 2012 at 12:44 pm
sql-lover (9/19/2012)
1st, apologies if this has been asked before. I remember I faced this issue few years ago, but do not remember exactly what my calculations were.Basically, I have a medium size database (over 100GB) that needs a reindex job for 1st time. I know for sure, it will hit space hard that 1st time, but I do have some space constraints so I may run out of space before the job completes.
How can I calculate the amount of space required for the Tlog so the job won't fail? I am currently calculating that with top 5 tables and adding a 10 to 20 percentage to that. Is that correct? I know the best way to know this is after the job runs, but that's not an option here. So I am looking for a ballpark number based on some math and educated guess.
Any other comment or suggestion is welcome.
Any chance of doing it during a maintenance window where you set recovery mode to simple? that would keep t-logs from growing.
_____________________________________
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.September 19, 2012 at 1:08 pm
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?
September 19, 2012 at 2:04 pm
switching to bulk logged will minimally log for an index creation same as simple and won't break the log chain, if its a reorg its fully logged regardless 😉
So, are you rebuilding only or reorganising too?
Are you using an "intelligent" script to maintain the indexes?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
September 19, 2012 at 2:26 pm
This database has been running without jobs for years. And before you ask ... no, I was not the DBA before ... 🙂
-I am using smart ReIndex (Ola Hallengren solution)
-The ReIndex will run for 1st time
I'm just trying to do some simple math in order to ensure the job won't fail or fill up the drive, as I won't be checking the job realtime.
Thought about this...
Table A x 1.2 = min drive space for Tlog
Where Table A is the size of the biggest table in the database. So if that table is 20GB, to say something... 24GB at least?
September 19, 2012 at 2:35 pm
sql-lover (9/19/2012)
-I am using smart ReIndex-The ReIndex will run for 1st time
How do you know? Unless you force reindex you could end up reorging some indexes and this will bloat the log for sure!
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
September 19, 2012 at 2:40 pm
Perry Whittle (9/19/2012)
sql-lover (9/19/2012)
-I am using smart ReIndex-The ReIndex will run for 1st time
How do you know? Unless you force reindex you could end up reorging some indexes and this will bloat the log for sure!
Sorry, I mean, the job will run for 1st time. If it will do a reoganize or reindex, it depends.
However, based on the amount of pages (for the big tables) and the fragmentation I've seen inside, I am almost sure a ReIndex will be triggered, instead of a reorganize. This is not the 1st big database without a job that I am trying to put in good shape and that has been the default behavior so far. However, the Tlog resides on a logical drive that does not have plenty of space.
So, is my math above correct? 🙂
September 20, 2012 at 10:50 am
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.Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply