I normally use the following method to determine the free/used space within each file of a database:
Select *, fileproperty(name, 'SpaceUsed') as Used From dbo.sysfiles
This returns total and used space in pages, which I then multiply by 8 to get KB (or divide by 128.0 to get MB).
I found another script than instead uses
DBCC showfilestats and
dbcc sqlperf(logspace) to return the TotalExtents and UsedExtents, which can then be multiplied by 64 to get KB (or divided by 16.0 to get MB).
Ignoring the extra columns, will these two always give identical values for free/total space? What about
Does their accuracy both depend on a recent DBCC UPDATEUSAGE?
Is there another, better method for determining used/free space? (I need this script to work on SQL 2000, 2005, and 2008 servers)
Partially related: Can you have a partially-allocated extent? (only 3 of the 8 pages within an extent are allocated, for example)