the database is 298 GB in its MDF size and has grown as a result of last weekened REINDEX process which like any other weekend it has done but significantly grown to its corresponding MDF location. The actually size is 240 GB before the Reinxed process
But this sunday morning it has grown to 58 GB more in its MDF.
This needs to be shrinked and the attempt was made, instead of shrinking it has again grown and this extra growth seriously a threat to the existing disk space available.
Infrastructure has denied any allocation to its disk space instead the SHRINK must fix this problem.
what is the best method to shrink can anyone at the earliest suggest the correct idealogy to perform this task.
Bit surprised that a reindex would add 20% to the size - is there a single large table with many indexes? How much free space is there in the database? You could try a shrink with reorg.
========================================== Cursors are useful if you don't know sql. SSIS can be used in a similar way. Beer is not cold and it isn't fizzy.
298GB with 52GB free space doesn't sound too bad. If that is filling your disk I would be more worried about space in general.
When you shrink a file there is an option to reorganise.
========================================== Cursors are useful if you don't know sql. SSIS can be used in a similar way. Beer is not cold and it isn't fizzy.
In enterprise manager right click on the database. tasks, shrink, files. You need to look at data files (I assume you only have one). Check the free space percent. If this is not large then you are stuck. Under shrink action select to reorganise before shrinking. The shrink file to value should default to the minimum. Click ok - wait for it to finish (have a few cups of tea) and see how much it has released.
========================================== Cursors are useful if you don't know sql. SSIS can be used in a similar way. Beer is not cold and it isn't fizzy.
The shrink has been done but haven't used the REORGANISE (since shrink fixed the data files back to normal, where we expected).
The following weekend again automated Reindex process has again grown the MDF to 90 GB more than its original capacity (last time it has grown 58 GB of its MDF) and LDF to 90 MB (Considering it small with respect to its MDF) however, I couldn't understand out of all these weeks the reindex hasn't cost to grow its underlying MDF but why it has to grow.
Should I DISABLE this REINDEX automated process permanently
OR
should change with REORGANISATION with every week
As I never experienced this behaviour in the past over the years to grow the Data Files as a result of REINDEX process.
Could you please consider this and still advise me and thanking you much for your time and suggestions which are really valuable. :)
SHRINK
Posted - 01/07/2013 : 20:39:26
