Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 Required disk space to reindex Rate Topic Display Mode Topic Options
Author
 Message
 Posted Wednesday, September 19, 2012 12:03 PM
 SSC Veteran Group: General Forum Members Last Login: Today @ 1:58 PM Points: 280, Visits: 996
 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.
Post #1361550
 Posted Wednesday, September 19, 2012 12:44 PM
 Hall of Fame Group: General Forum Members Last Login: Saturday, December 07, 2013 7:47 PM Points: 3,067, Visits: 4,620
 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) BerzukovAuthor 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.
Post #1361570
 Posted Wednesday, September 19, 2012 1:08 PM
 SSC Veteran Group: General Forum Members Last Login: Today @ 1:58 PM Points: 280, Visits: 996
 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?
Post #1361581
 Posted Wednesday, September 19, 2012 2:04 PM
 SSCertifiable Group: General Forum Members Last Login: Yesterday @ 2:01 PM Points: 5,464, Visits: 11,820
 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"
Post #1361611
 Posted Wednesday, September 19, 2012 2:25 PM
 SSC Veteran Group: General Forum Members Last Login: Today @ 1:58 PM Points: 280, Visits: 996
 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 timeI'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?
Post #1361619
 Posted Wednesday, September 19, 2012 2:35 PM
 SSCertifiable Group: General Forum Members Last Login: Yesterday @ 2:01 PM Points: 5,464, Visits: 11,820
 sql-lover (9/19/2012)-I am using smart ReIndex-The ReIndex will run for 1st timeHow 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"
Post #1361623
 Posted Wednesday, September 19, 2012 2:40 PM
 SSC Veteran Group: General Forum Members Last Login: Today @ 1:58 PM Points: 280, Visits: 996
 Perry Whittle (9/19/2012)sql-lover (9/19/2012)-I am using smart ReIndex-The ReIndex will run for 1st timeHow 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?
Post #1361625
 Posted Thursday, September 20, 2012 10:50 AM
 Hall of Fame Group: General Forum Members Last Login: Saturday, December 07, 2013 7:47 PM Points: 3,067, Visits: 4,620
Post #1362154

 Permissions