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 |
|
pdset
Constraint Violating Yak Guru
310 Posts |
 Posted - 2011-03-28 : 22:35:53
|
| Sometime back it was advised as a result of the REINDEX process there itself being the transaction and corresponding MDF and LDF would be increased and this increase should be maintained.However, on some databases the MDF has been increased and on some databases there is no increase on MDF but only on LDF only - as a result of REINDEX process.Can you explain why like this it should happen.If there is increase as a result on LDF this should be maintained, however, there is increase onto MDF then should it be SHRINKED thereafter?What needs to be done to keep the MDF to its original size even after the REINDEX process occurs on weekly basis.On the otherhand, I see the MDF is grown and LDF also grown, but when I see the Actual size of the database is reflected on my .BAk file and also from the DB properties.How to maintain the MDF to its actual size?Thanks All for your suggestions. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
pdset
Constraint Violating Yak Guru
310 Posts |
Posted - 2011-03-29 : 18:54:40
|
| Since it is clearly evident that size has grown as a result of the REINDEX process; and this space can be gettable.My other question, why some databases register the growth on MDF but not LDF; and vice-versa.Thanks all. |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
pdset
Constraint Violating Yak Guru
310 Posts |
Posted - 2011-03-31 : 22:35:20
|
| Before the Reindex the DB MDF size was 37 GB and .BAK size was 37 GB and after Reindex MDF size to 42 GB and .BAK size to 37 GB.But strangely there is no increment on LDF before and after.On the other server this is other way.Before the Reindex the DB LDF size was 2 GB and .BAK size was 45 GB and after Reindex LDF size to 15 GB and .BAK size to 45 GB.But strangely there is no increment on MDF before and after.How to control this unwanted rise in size on the data files.Thanks for your time. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
mmarovic
Aged Yak Warrior
518 Posts |
Posted - 2011-04-01 : 18:35:27
|
| When the space allocation does not increase, it means there was a free space inside the file before reindexation started.I agree with Tara. Since you will need the space next time you reindex, there is no sense to shrink. Also db grows so you will need that space sooner or later even without (re)indexation. On top of that, shrink fragments data and indexes again, so after shrink you might end up with worst fragmentation then before you started reindexing. Even worst, when you shrink, you can expect file expand soon, even without reindexation, typically at the time there are the most transactions and that expansion will slow down processing of your server, which can be recognized by connection timeouts at that time.So, do not shrink, ever!MirkoMy blog: http://mirko-marovic-eng.blogspot.com/ |
|
|
|
|
|
|
|
|
REINDEX after