DB size

Question

Post reply

DB size

ramana3327

SSCoach

Points: 19722
More actions
June 5, 2015 at 4:29 pm

#318052

What is the difference b/n size & space available when you right click on database properties?

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Jacob Wilkins One Orange Chip Points: 27976 More actions · Answer 1

They're more or less what it says on the tin 🙂

Size is just the total size of the database files on disk.

Space available is the total free space within the database files.

Cheers!

ramana3327 SSCoach Points: 19722 More actions · Answer 2

so if the datafile size is higher than database size i.e. there is more unused space right? What is the use of that unused space

Jacob Wilkins One Orange Chip Points: 27976 More actions · Answer 3

The main point of having available unused space is to prevent costly autogrowth events.

So, let's say you have a database file with just one data file and one log file.

The data file is 1 GB and has 100 MB free/available space, while the log file is 500 MB and has 50 MB free/available space.

In that case, what you're looking at (the properties of the database in SSMS), would show a size of 1.5 GB and 150 MB free space.

If you didn't have any available space in either file, when you either added data or wrote to the log, the respective file would have to grow to allow the operation to continue. That growth doesn't come for free (especially with log files, which can't benefit from instant file initialization).

That's why it's best to size files to accommodate expected growth over the near future (at least), if at all possible.

Cheers!

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 4

You also need some headroom for certain maintenance such as REBUILDing indexes. Any index over 128 extents (that's just 8 megabytes) that needs to be rebuilt will first be rebuilt and, once the new version of the index has been committed, only then will the old index be dropped. If you do it in an online fashion, there's also a "transaction" working table that is built to keep track of things that happen while the rebuild is occurring.

Once those are gone, the extra "unused" space will remain and that's usually not a bad thing.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ramana3327 SSCoach Points: 19722 More actions · Answer 5

I am little confused here.

Database is size is only .mdf file size. I thought free space is available means it tells us the free space available in the data file.

Is it includes the log size?

Jacob Wilkins One Orange Chip Points: 27976 More actions · Answer 6

If we're talking about the values for Size and Space Available when you right-click a database and look at the General tab in Properties, then yes, the value for Size is the sum of the size of data and log files, not just data files.

Space Available may only be free space in data files, not logs. I never use the GUI for this (I usually use sys.database_files and FILEPROPERTY), so I'm not completely sure. Some preliminary tests indicate that it is indeed only free space in data files, while Size is total size of data and log. Weird, and yet another reason not to use the GUI 🙂

Probably it's using the same calculations as sp_spaceused, which seems to do the same, as documented here: https://msdn.microsoft.com/en-us/library/ms188776.aspx.

database_size will always be larger than the sum of reserved + unallocated space because it includes the size of log files, but reserved and unallocated_space consider only data pages.

(emphasis mine)

Cheers!