Sql-server – Database file size from sys.master_files and sys.dm_io_virtual_file_stats are different

sql-server-2005sql-server-2008sql-server-2008-r2

Im using query to find out database file size. I'm using system view and DMV, because i do want to find out both- actual file size and theoretical (in case of sparse files) file size.

Select 
    DB_NAME(mf.database_id) AS [Database Name], 
    mf.Name,  
    mf.physical_name PhysicalName,
    cast(mf.size as bigint) * 8192 mfSize_bytes, 
    fs.size_on_disk_bytes fsSize_bytes
From sys.master_files mf
    Left Join sys.dm_io_virtual_file_stats(DEFAULT, DEFAULT) fs 
        On mf.database_id = fs.database_id and mf.file_id = fs.file_id
Order By DB_NAME(mf.database_id)

I am confused- converted both values to "bytes", comparing them. But on all instances i checked- temdb file size (from sys.master_files) is smaller than file size on disk (from sys.dm_io_virtual_file_stats).

In all other cases if there is difference, then that is correct (size on disk is actually smaller than file size- because they are sparse files).

What is the reason for this difference?

UPDATE:
When i am querying

Select is_sparse, * From sys.database_files

On SQL Server 2005 database snapshot, then column is_sparse = 0 (because it is showing properties of the files from original database, not snapshot file. So- is there bug in documentation?).

Best Answer

Just from looking at the query results on my local development instance, there are two differences that I see (there are probably more):

  1. sys.master_files returns the "virtual" size of a file, while sys.dm_io_virtual_file_stats returns the on-disk size of the file. I only see different sizes for database snapshots, which use NTFS sparse files behind the scenes (and I did verify the allocated and on-disk sizes in Windows).

  2. sys.dm_io_virtual_file_stats does not return sizes for databases that are offline, while sys.master_files does.

For the case of tempdb, sys.master_files contains the startup file sizes for tempdb. If you investigate sys.database_files in the context of tempdb, you'll see the current sizes.

Related Question