Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
dejjan
Yak Posting Veteran
99 Posts |
 Posted - 2005-08-29 : 06:34:47
|
| Hello,I've just done rebuild indexes in one DB. I expected log to rise but I didn't expected data file to rise so much.Before rebuilding: data_files..used:2136MB...unused:165MBlog_files....used:5MB.......unused:16MBAfter rebuilding:data_files..used:2710MB...unused:1775MBlog_files....used:25MB......unused:2600MBHow could I predict amount of space for rebuild indexes. I thought only log file will rise. |
|
|
Kristen
Test
22859 Posts |
Posted - 2005-08-29 : 06:52:56
|
Looks like you have some FILL FACTOR on your indexes which are very lightly populated, thus REINDEX has re-instated that FILL FACTOR which had got erroded by data being added.Running the reindex regularly shouldn't have such a dramatic effect!Are You sure you've quoted the figured correctly?before: data_files..used:2136MB...unused:165MB, space used = 2136-165 = 1971After: data_files..used:2710MB...unused:1775MB, space used = 2710-1775 = 935 <=== Kristen |
 |
|
|
dejjan
Yak Posting Veteran
99 Posts |
Posted - 2005-08-29 : 07:08:06
|
| That DB consist of only 5 tables, where only one is huge .. and that one have fill factor for one index of 70% (there are only 2 indexes in that table .. the other one is with fill factor 0)I didn't make mistake about space.Is there a solution to calculate how much space I need for rebuilding (approximately) |
|
|
|
dejjan
Yak Posting Veteran
99 Posts |
Posted - 2005-08-29 : 09:17:36
|
| Is there any procedure, or whatever, which could give me an information about used and unused space for data file. Same as it is showen in EM when I click view/taskpad for specific DB. Smth like DBCC sqlperf(logspace) for log data. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-08-29 : 12:07:29
|
| If you have a fillfactor of, say, 70% on a file it strikes me that it would be pretty hard work predicting how much space will be needed for a reindex if it hasn't been done for ages - it would depend on how full each index block has become, compared to how much space is going to be added as padding.I'm also concenered that your database went from 1,971MB used to 935MB doing a reindex - I can't figure how that is possible, but maybe I'm being thick!Do the reindex every day and the impact on MDF will be minimal (although you'll need to allow enough space for LDF size - conventionally 120% of MDF for a full reindex without taking any steps to limit LDF size, I believe.Kristen |
|
|
|
dejjan
Yak Posting Veteran
99 Posts |
Posted - 2005-08-30 : 03:32:08
|
| Kirsten, I don't understand your calculation. I said:before: data_files..used:2136MB...unused:165MB, that gives: space used = 2136+165 = 2301After: data_files..used:2710MB...unused:1775MB, that gives: space used = 2710+1775= 4485 |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-08-30 : 09:00:42
|
| <Ding!> Gotcha, I though the "unused" was the "total" and that cotained the "unused", sorry.That aside, my other point was that if you've got a fill factor of 70% it will be hard to predict the growth, and I would just run the rebuild often so that the growth is modest (also a good thing to do it often to keep the indexes in the "shape" that you've set them to).Kristen |
|
|
|
|
|
|
|
|
Rebuild indexes