InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comTue, 18 Jun 2013 20:23:42 GMT20http://www.sqlservercentral.com/Forums/Topic1361550-1550-1.aspx[quote][b]sql-lover (9/19/2012)[/b][hr][quote][b]PaulB-TheOneAndOnly (9/19/2012)[/b][hr]Any chance of doing it during a maintenance window where you set recovery mode to simple? that would keep t-logs from growing.[/quote]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?[/quote]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.Thu, 20 Sep 2012 10:50:58 GMTPaulB-TheOneAndOnlyhttp://www.sqlservercentral.com/Forums/Topic1361550-1550-1.aspx[quote][b]Perry Whittle (9/19/2012)[/b][hr][quote][b]sql-lover (9/19/2012)[/b][hr]-I am using smart ReIndex-The ReIndex will run for 1st time[/quote]How do you know? Unless you force reindex you could end up reorging some indexes and this will bloat the log for sure![/quote]Sorry, I mean, the [b]job[/b] 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? :-)Wed, 19 Sep 2012 14:40:11 GMTsql-loverhttp://www.sqlservercentral.com/Forums/Topic1361550-1550-1.aspx[quote][b]sql-lover (9/19/2012)[/b][hr]-I am using smart ReIndex-The ReIndex will run for 1st time[/quote]How do you know? Unless you force reindex you could end up reorging some indexes and this will bloat the log for sure!Wed, 19 Sep 2012 14:35:29 GMTPerry Whittlehttp://www.sqlservercentral.com/Forums/Topic1361550-1550-1.aspxThis 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... [code="plain"]Table A x 1.2 = min drive space for Tlog[/code]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?Wed, 19 Sep 2012 14:25:59 GMTsql-loverhttp://www.sqlservercentral.com/Forums/Topic1361550-1550-1.aspxswitching 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?Wed, 19 Sep 2012 14:04:22 GMTPerry Whittlehttp://www.sqlservercentral.com/Forums/Topic1361550-1550-1.aspx[quote][b]PaulB-TheOneAndOnly (9/19/2012)[/b][hr]Any chance of doing it during a maintenance window where you set recovery mode to simple? that would keep t-logs from growing.[/quote]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?Wed, 19 Sep 2012 13:08:55 GMTsql-loverhttp://www.sqlservercentral.com/Forums/Topic1361550-1550-1.aspx[quote][b]sql-lover (9/19/2012)[/b][hr]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.[/quote]Any chance of doing it during a maintenance window where you set recovery mode to simple? that would keep t-logs from growing.Wed, 19 Sep 2012 12:44:42 GMTPaulB-TheOneAndOnlyhttp://www.sqlservercentral.com/Forums/Topic1361550-1550-1.aspx1st, 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.Wed, 19 Sep 2012 12:03:48 GMTsql-lover