Database file size calculation

Question

Database file size calculation

info.sqldbamail

SSCommitted

Points: 1741
More actions
August 29, 2017 at 6:54 am

#338895

Hello Experts,
I have a question, While finding the current size of database file using system object sys.master_files why do we need to multiply the column size by 8 , According to books online it is showing as shown in below image , File size, in 8-kilobyte (KB) pages.
Below image says file size if it is a data file size, then is it showing the number in bytes/bits/kilobytes.

Below screenshot shows a number 2224288 what does this mean size in Bytes/Kilobytes, Why do we need to multiply with 8 to get actual size of database file?
1) Is there any relation with allocation_units total_pages?
2) Can anyone tell how does this size being displayed here from sys.sysbrickfiles.
Looking forward for your answers.
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Save a tree... Please don't waste paper unless you really need to!
When life puts into problem don't say 'God Why Me' say:w00t: 'Try Me'

Viewing 5 posts - 1 through 4 (of 4 total)

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

jasona.work SSC Guru Points: 50117 More actions · Answer 1

You need to multiply by 8 because the count in the size column is in pages. Each page (of data stored) = 8KB.
So 1 page = 8KB
Your example database then is 2224288 pages x 8KB/page = 17794304KB = 17377MB = 16.9GB on disk.

Ant-Green SSC Guru Points: 113504 More actions · Answer 2

Stay away from sys.sysbrickfiles, it is used by sys.sysaltfiles and they are marked for depreciation and are intended for backwards compatibility.
The documentation for sys.sysbrickfiles says you can only get to it via the DAC and is intended for Microsoft's use, so use it at your own risk. Stick to the new system catalog views.

info.sqldbamail SSCommitted Points: 1741 More actions · Answer 3

Hello Jasona,

Thanks for your answer, Do you mean to say those are no: of pages that is same as total_pages_count in sys.dm_db_file_space_usage, But why did Microsoft gave column name as size, Is there a specific reason behind it.

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Save a tree... Please don't waste paper unless you really need to!

When life puts into problem don't say 'God Why Me' say:w00t: 'Try Me'

jasona.work SSC Guru Points: 50117 More actions · Answer 4

info.sqldbamail - Tuesday, August 29, 2017 7:27 AM
Hello Jasona,
Thanks for your answer, Do you mean to say those are no: of pages that is same as total_pages_count in sys.dm_db_file_space_usage, But why did Microsoft gave column name as size, Is there a specific reason behind it.

Yes, that is correct.
As for the *why* MS did that? You'd have to track down one of the coders responsible, I have no idea. Likely it falls into the category of things "that seemed like a good idea at the time."