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?).