In SSMS I saw file size related properties and found below details for one database. Here values does not match with other properties. Here size of the mdf, ldf and total size matches with other values under each window. But Available free space of mdf and ldf if added then it does not equal to Available free space shown in shrink database window and free space shown in database properties. This is true for any database. Why is it so? Please can anyone explain the logic behind this?
Under database properties:
Size: 91.31 MB
Space available:13.40 MBUnder database file properites:
mdf size: 17 MB
ldf size: 75 MBunder shrink database:
Currently allocated size: 91.31 MB
Available free space: 13.40 MBunder shrink file-for data file:
currently allocated size: 16.38 MB
Available free space: 12.63 MBunder shrink file-for log file:
currently allocated size: 74.94 MB
Available free space: 55.62 MB
Best Answer
This doesn't really seem all that crazy, but note that some of the UI dialogs might not have completely up-to-date information (this is why we have things like DBCC UPDATEUSAGE), and rounding can also be involved in some of those calculations. Finally, the dialogs show you total space for the whole database, but unallocated space is only calculated for the data files, not the log.
Let's coalesce some things.
Let me take a look at what these different dialogs run for my local copy of AdventureWorks2012 (with certain tables enlarged from this script).
This returns (first resultset only):
Essentially runs this, which - I've confirmed via trace - is roughly the same query executed from the database properties and database shrink dialogs (I've carved out the irrelevant parts from the stored procedure, and added an outer query to represent the math that SSMS does for display):
This returns a match:
These dialogs all show this information correctly. Database Properties dialog:
Shrink Database dialog:
The shrink file dialogs, on the other hand, run a slightly different query (again this is carved/adapted for convenience):
Note, too, that in addition to getting size data from a function instead of a DMV, the predicates have not been updated for new file types, like filestream/hekaton.
Results:
The problem is the
FILEPROPERTY()
function, which is not guaranteed to be up to date (even afterDBCC UPDATEUSAGE(0);
is run; more below). This ends up with this misleading information on the dialogs:Note, again, that 6.67 MB was never really accurate, since this is only measuring the total database size - the number of pages allocated, completely disregarding the log.
In all honesty, if you want accurate reporting of space used in the database, stop using the mickey mouse UIs which run all kinds of different queries to figure this out, and stop using the shrink file dialogs for retrieving information. These are clearly subject to stale data problems in certain cases. Run an actual query against a source you can trust. Here is what I prefer:
This query returns three numbers that should look very familiar, and one that should not:
Note that DBCC SQLPERF is also slightly prone to issues with space usage, for example after running:
The above query yields this instead:
sp_spaceused
now yields matching numbers as well (1545.81 MB / 8.08 MB
), even though - again - that is only the space available in the data file(s), and the database property and database shrink dialogs are "accurate" as well (but the shrink file dialogs are still way off -FILEPROPERTY()
does not seem to be affected byUPDATEUSAGE
at all):Oh, and might as well show what Windows Explorer thinks of these files, so you can relate to the calculations made to determine MB:
How accurate this all needs to be, of course, depends on what you're going to do with the information.