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 - 2013-01-07 : 20:39:26
|
| 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.Many Thanks in advance. |
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2013-01-08 : 04:11:49
|
| 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. |
 |
|
|
pdset
Constraint Violating Yak Guru
310 Posts |
Posted - 2013-01-08 : 16:51:10
|
| Currently Database size is 298GB and Free space 52925MB and 1 table have couple of indexes where the main transactions are placed heavily.Do you suggest SHRINK followed by REORGANISATION OF INDEXES ?Thanks for your time. |
|
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2013-01-09 : 03:26:33
|
| 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. |
|
|
|
pdset
Constraint Violating Yak Guru
310 Posts |
Posted - 2013-01-09 : 23:25:23
|
| I have used SHRINKDATABASE option and couldn't get the automated REORG for that.As shrinking performed instead of shrink of the file it got increased another 52 GB to the 298 GB Whooping 350GB now.Any suggestion please |
|
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2013-01-10 : 03:57:18
|
| 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. |
|
|
|
pdset
Constraint Violating Yak Guru
310 Posts |
Posted - 2013-01-12 : 15:56:46
|
| Thanks for your DETAILED script / analysis.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 permanentlyOR should change with REORGANISATION with every weekAs 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