x

Understanding Available Free Space on the Shrink Database Screen

On the shrink database screen there is a value for available free space. Yesterday it read 2%. I ran a shrink database and the value turned to 0%. What does available free space on this screen represent.
more ▼

asked May 17 '12 at 03:24 PM in Default

sczarnomski gravatar image

sczarnomski
0 1 1 1

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

4 answers: sort voted first
Essentially it represents how much space there is above and beyond data. Space that can presumably be removed during a shrink operation and returned to the file system.
more ▼

answered May 17 '12 at 03:44 PM

Blackhawk-17 gravatar image

Blackhawk-17
11.8k 28 30 35

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

Greg is completely right. But another way of looking at it that might be useful is:

The free space represents how much more data can be added before the database will have to either autogrow or return errors for lack of space.
more ▼

answered May 17 '12 at 05:05 PM

TimothyAWiseman gravatar image

TimothyAWiseman
15.5k 19 22 32

(comments are locked)
10|1200 characters needed characters left
Make sure you know do some index maintenance to repair all the fragmentation you did with removing the 2% of free space. Greg and and Tim are 100% correct. It is the amount of free space in the file for additional data. You typically want this unless it is a read only database. You typically don't want the data file to auto grow unless you have instant file initialization on as this causes performance issue when the data file has to grow. Depending on the size of the database 2% isn't a great deal. My low water mark is 5% for most of my databases. It really all depends on growth patterns.
more ▼

answered May 17 '12 at 10:05 PM

Tim gravatar image

Tim
35.5k 32 41 138

(comments are locked)
10|1200 characters needed characters left
Thanks to everyone who responded. This was very helpful. I thought it might be something like that!
more ▼

answered May 18 '12 at 12:04 PM

sczarnomski gravatar image

sczarnomski
0 1 1 1

(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:

x21

asked: May 17 '12 at 03:24 PM

Seen: 1677 times

Last Updated: May 18 '12 at 12:04 PM