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.

 All Forums
 SQL Server 2000 Forums
 SQL Server Administration (2000)
 SHRINK

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.
Go to Top of Page

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.
Go to Top of Page

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.
Go to Top of Page

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
Go to Top of Page

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.
Go to Top of Page

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 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. :)


Go to Top of Page
   

- Advertisement -