I am currently at a company where there is a problem that a database has got to much unused space. If I shrink the file and the database, then reorganize the index's would that release the unused space? Or is there another better solution?
asked Jun 06, 2012 at 12:12 PM in Default
I guess the first question we have is where is there free space? Is it in the data file(s) or the transaction log?
If you expect the data to grow you're better off leaving the file with free space as this reduces possible file fragmentation later. If it will grow only slightly within your forecast horizon then go ahead and shrink it, leaving some headroom for ongoing operations and to avoid an auto-growth later.
If the transaction log has a ton of free space consider performing T-Log backups more frequently to limit the growth going forward after shrinking.
If the database has become somewhat static then rebuild the indexes with a fill factor of 0 and then shrink the data file. The rebuild itself doesn't release space, it only frees pages within the file that can be removed via a shrink.
Shrink database basically performs a shrink file against the data and the transaction log so there is no reason to do both. Shrink file gives you more granular control.Whatever route you choose just keep in mind that active databases tend to grow.
answered Jun 06, 2012 at 01:11 PM
I suggest you read Paul Randal's Blog post [Why you should not shrink your data files].
He explains why it is not a good thing to shrink data files and also gives some alternatives to shrink.: http://www.sqlskills.com/blogs/paul/post/why-you-should-not-shrink-your-data-files.aspx
answered Jun 06, 2012 at 02:06 PM