x
login about faq Site discussion (meta-askssc)

Unused space

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?

more ▼

asked Jun 06 '12 at 12:12 PM in Default

JpFernhout gravatar image

JpFernhout
10 1 1

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

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.

more ▼

answered Jun 06 '12 at 01:11 PM

Blackhawk-17 gravatar image

Blackhawk-17
10.5k 23 29 34

(comments are locked)
10|1200 characters needed characters left

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.

more ▼

answered Jun 06 '12 at 02:06 PM

Pavel Pawlowski gravatar image

Pavel Pawlowski
20.3k 5 10 20

but if the growth is down to a problem and is now compromising other systems then the database will have to be shrunk.

Jun 06 '12 at 02:10 PM Fatherjack ♦♦
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments



Facebook logo Follow Ask SSC on Facebook
Find Ask SSC on Google+
linkedin logo Find us on LinkedIn

Topics:

x33
x18

asked: Jun 06 '12 at 12:12 PM

Seen: 394 times

Last Updated: Jun 06 '12 at 02:10 PM

Copyright © 2002-2012 Simple Talk Publishing. All Rights Reserved. If you have any queries, please contact the site administrators.
Ask SQL Server Central is a community service provided by Red Gate.