Sometimes, we can see the database file size show different size than it is actual on the host. The available free space is even negative.
In this example, my tempdb shows only have 0.5 MB and the available free space is –1.00MB. How could it be?
After some research and particular thanks for this post which make things much clear.
Story in short, the current allocate space number in SSMS is from sys.master_files which could be stale if the database has the auto grow turn on. The available free space is calculate base on below formula :
(current allocate space from sys.master_files – fileproperty from sys.database_files ) / 1024
- The fileproerty function would scan GAM page which would always give the most recent used size.
- The sys.database_files is system table specific for each database.
Here is the sample query which you can prove this
declare @filesize decimal(18,4)
declare @usedsize decimal(18,4)
declare @free decimal(18,4)
select @filesize = size * 8
where database_id = db_id('tempdb');
select @usedsize=fileproperty(name,'spaceused') * 8
from tempdb.sys.database_files where file_id =2;
select @filesize as "File size KB"
select @usedsize as "File used KB"
select @free = ( @filesize - @usedsize) /1024
select @free as "Available Free MB"
Inaccurate values for “Currently allocated space” and “Available free space” in the Shrink File dialog for TEMPDB on: http://blogs.msdn.com/b/ialonso/archive/2012/10/08/inaccurate-values-for-currently-allocated-space-and-available-free-space-in-the-shrink-file-dialog-for-tempdb-only.aspx