Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Required disk space to reindex Expand / Collapse
Author
Message
Posted Wednesday, September 19, 2012 12:03 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 11:15 AM
Points: 384, Visits: 1,270
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

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, January 28, 2014 8:15 AM
Points: 3,068, Visits: 4,639
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.
Post #1361570
Posted Wednesday, September 19, 2012 1:08 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 11:15 AM
Points: 384, Visits: 1,270
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

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 10:50 AM
Points: 6,358, Visits: 13,688
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
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 11:15 AM
Points: 384, Visits: 1,270
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?
Post #1361619
Posted Wednesday, September 19, 2012 2:35 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 10:50 AM
Points: 6,358, Visits: 13,688
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"
Post #1361623
Posted Wednesday, September 19, 2012 2:40 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 11:15 AM
Points: 384, Visits: 1,270
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?
Post #1361625
Posted Thursday, September 20, 2012 10:50 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, January 28, 2014 8:15 AM
Points: 3,068, Visits: 4,639
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.
Post #1362154
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse