February 11, 2014 at 6:12 am
I'm working on an inhouse database growth monitor using sys.database_files. I want to compare my numbers to the actual sizes I'm seeing in Database Properties (gotten through right-clicking the database) and noticed that the Size property is actually all files (data and log combined). Okay, that's easy. But when I look at Space Available, I can't come up with any combination of numbers that makes sense with the number I'm seeing displayed...
Master database properties: Size = 34.50 MB, Space Available: 20.24 MB
Master file information: Data size: 3072 (24576 KB, which matches the size of the .mdf file on disk), Log size file: 1344 (10752, which matches the size of the .ldf file on disk).
Space Used calculated using FILEPROPERTY(Name,'SpaceUsed'): Data file 496, Log file 77.
Here's where it gets hinky.
If I'm reading the BOL FILEPROPERTY entry correctly, it returns the pages, not the actual KB. So KB space used is 3968 for Data and 616 for Log. They total out to 4584 KB or 4.4765625 MB. Subtracting that number from 34.50 does NOT equal the GUI Space Available of 20.24 MB. In fact, even just subtracting the data file space used is off by a small number (24.00 - 3.875 = 20.125, which is a difference of .115).
Yeah, I know I'm getting nitpicky, but I want those numbers to match as closely as possible. Anyone know what I'm missing?
Here's some code to show you what I'm comparing the GUI against.
SELECT
a.file_id , a.name, a.size, (a.size * 8) AS SizeInKB,
(a.size * 8) / 1024 AS SizeInMB, --checking my math for other MB calc
CONVERT(DECIMAL(12,4),ROUND(a.size/128.0000,4)) AS [file_size_mb],
CONVERT(DECIMAL(12,4),
ROUND(FILEPROPERTY(a.name,'SpaceUsed')/128.0000,4) ) AS [space_used_mb] ,
CONVERT(DECIMAL(12,4),
ROUND((a.size-FILEPROPERTY(a.name,'SpaceUsed'))/128.0000,4)) AS [SpaceAvailableMB],
FILEPROPERTY(a.name,'SpaceUsed') AS SpaceUsed,
FILEPROPERTY(a.name,'SpaceUsed') * 8 AS KBSpaceUsed,
--Google converts this to 3.875 MB, so it looks correct
(FILEPROPERTY(a.name,'SpaceUsed') * 8) / 128.0000 AS MBSpaceUsed,
--This is a bad calculation
((FILEPROPERTY(a.name,'SpaceUsed') * 8) / 128.0000) / 128.0000 AS MBSpaceUsed2
--this also appears to be bad calc
FROM
sys.database_files a
February 11, 2014 at 7:03 am
Brandie,
I've always divided by 1024 when going from KB to MB.
I just tried your query against one of my DBs, replacing:
(FILEPROPERTY(a.name,'SpaceUsed') * 8) / 128.0000 AS MBSpaceUsed,
with
(FILEPROPERTY(a.name,'SpaceUsed') * 8) / 1024 AS MBSpaceUsed,
and it comes out significantly closer (only off by ~.5MB from the SizeInMB column.
It also looks like the "Space available" reported in the GUI is only in the MDF, not the LDF, as well.
February 11, 2014 at 7:44 am
I started off using 1024, but the math still doesn't work out correctly for what I'm seeing. (FYI: I got the 128 from a couple of different blog posts. Here's one that I didn't trust[/url] until I started actually looking at the numbers.)
Going back to my code... With 3072 pages, I get 24576 KB (3072 * 8) or 24 MB at (3072 * 8) / 1024. That makes sense and it works.
But when I take the FILEPROPERTY with SpaceUsed....
SELECT CONVERT(DECIMAL(12,4),ROUND(FILEPROPERTY(a.name,'SpaceUsed')/128.0000,4) )
AS [space_used_mb128] , --3.8750
CONVERT(DECIMAL(12,4),ROUND(FILEPROPERTY(a.name,'SpaceUsed')/1024.0000,4) )
AS [space_used_mb1024] --.4844
FROM sys.database_files
Taking 24 - 0.4844, I get 23.5156. That is nowhere near the 20.24 MB I'm seeing as Space Available in the GUI (picture attached). And I don't see any negative numbers in the space used for the log (.6016 or .0752 respectively).
This is beyond frustrating. I'm trying to figure out why the 128 number is working better than the 1024 number and why neither of them gives me closer numbers.
February 11, 2014 at 8:01 am
Hi Brandie,
Went through a similar headache-inducing process myself a few months ago!
IIRC, Space Available = Total Size - Size of Log - SpaceUsed in data file.
Gaz
February 11, 2014 at 12:38 pm
Gazareth has it right. The space available does NOT include the LDF / Log file space. If you add up the space available + space used results for the MDF, it should add up to the size of the MDF.
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply