Database size in sys.database_files VS. size in sp_databases

Question

Post reply

Database size in sys.database_files VS. size in sp_databases

oskargunn

SSCommitted

Points: 1951
More actions
June 3, 2010 at 9:14 am

#140641

I am working at a firm where for years a script has been using <dbname>.sys.database_files to display the size of a particular database (this view displays the size of each data file )
I have a database which has 2 datafiles and this view returns 728 and 128,
which I interpret as 728+128=856/100= 8.56 Mb
However(!), when I look at the properties of the database, the size shows up as 6,69 Mb
Also when I run "EXEC sp_datbases", the size column for that particular database return 6848, which is then probably 6848/1024=6.6875 Mb, which corresponds to the properties size.
So then I begin to wonder, what size is being returned in <dbname>.sys.database_files ?

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

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

WayneS SSC Guru Points: 95460 More actions · Answer 1

It's fully explained in BOL under sys.database_files

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

oskargunn SSCommitted Points: 1951 More actions · Answer 2

Yeah, there it says:

Current size of the file, in 8-KB pages

Still I don´t really understand the difference :/

Shawn Melton SSC-Insane Points: 24685 More actions · Answer 3

oskargunn (6/3/2010)
Still I don´t really understand the difference :/

Well with a little bit of math calculations you get the explanation.

...first the output from my instance of the master database on a SQL Server 2005 instance I maintain:

USE master

SELECT * FROM sys.database_files

--Returns results with the information:

--MDF file = 179736

--LDF file = 4912

EXEC sp_databases

--Restult of master

--Size = 1442.56KB

--as stated by BOL, returned value in KB

exec sp_spaceused

--Result of master

--database_size = 1442.56MB

Then reviewing the database properties window of the master database I find:

size = 1442.56MB.

----------------------------------------------

So the sys.database_files outputs the size in 8KB pages so you take the number times 8, since 1 page is each 8 KB in size:

MDF file is 179736*8 gives me the size in KB = 1,437,888 KB divide by 1024 to get MB = 1404.19 MB

LDF file is 4912*8 gives me the size in KB = 39,296 KB divide by 1024 to get MB = 38.75 MB

TOTAL = 1,404.19 MB + 38.75 MB = 1,442.565 MB

Then sp_databases gives output in KB of 1,477,184 divide by 1024 to get MB = 1,442.5625 MB

Then sp_spaceused already in MB = 1,442.56 MB

Then DB Properties = 1,442.56MB

Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton

oskargunn SSCommitted Points: 1951 More actions · Answer 4

Ok select (size*8)/1024 from <dbname>.sys.database_files is the correct query then to get the size in Mb 🙂

Thank you very much for this nice explanation 🙂

m--S3qU3L Hall of Fame Points: 3706 More actions · Answer 5

-- System objects to find the Database file sizes

select sum(size)*8 from adventureworks.sys.database_files -- Gives current physical size correctly

select sum(size)*8 from adventureworks.sys.sysfiles -- Gives current physical size correctly

sp_databases -- Gives current physical size correctly

select sum(size)*8 from sys.master_files -- Doesn't give physical current size

where database_id=10

select sum(size)*8 from master..sysaltfiles -- Doesn't give physical current size

where dbid=10

John