patternMinor
Database file size from sys.master_files and sys.dm_io_virtual_file_stats are different
Viewed 0 times
master_filesfilearesizedatabasedm_io_virtual_file_statsdifferentsysandfrom
Problem
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.
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
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?).
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_filesOn 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?).
Solution
Just from looking at the query results on my local development instance, there are two differences that I see (there are probably more):
-
-
For the case of
-
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).-
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.Context
StackExchange Database Administrators Q#19555, answer score: 7
Revisions (0)
No revisions yet.