SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Administration (2000)
 SHRINK
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

pdset
Constraint Violating Yak Guru

297 Posts

Posted - 01/07/2013 :  20:39:26  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

United Kingdom
3383 Posts

Posted - 01/08/2013 :  04:11:49  Show Profile  Visit nigelrivett's Homepage  Reply with Quote
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

297 Posts

Posted - 01/08/2013 :  16:51:10  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

United Kingdom
3383 Posts

Posted - 01/09/2013 :  03:26:33  Show Profile  Visit nigelrivett's Homepage  Reply with Quote
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

297 Posts

Posted - 01/09/2013 :  23:25:23  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

United Kingdom
3383 Posts

Posted - 01/10/2013 :  03:57:18  Show Profile  Visit nigelrivett's Homepage  Reply with Quote
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

297 Posts

Posted - 01/12/2013 :  15:56:46  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000