x

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, 2012 at 12:12 PM in Default

JpFernhout gravatar image

JpFernhout
10 1 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, 2012 at 01:11 PM

Blackhawk-17 gravatar image

Blackhawk-17
11.9k 28 31 37

(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][1].

He explains why it is not a good thing to shrink data files and also gives some alternatives to shrink.

[1]: http://www.sqlskills.com/blogs/paul/post/why-you-should-not-shrink-your-data-files.aspx
more ▼

answered Jun 06, 2012 at 02:06 PM

Pavel Pawlowski gravatar image

Pavel Pawlowski
22.3k 9 11 21

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, 2012 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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

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

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x38
x22

asked: Jun 06, 2012 at 12:12 PM

Seen: 873 times

Last Updated: Jun 06, 2012 at 02:10 PM