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.

    DB_NAME(mf.database_id) AS [Database 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?).

  • On my instances the size is the same for tempdb on some, and different on others. Actually only the log file is different, and it is larger in sys.dm_io_virtual_file_stats - which seems opposite to what you are seeing. I believe the latter if we're talking about actual size on disk rather than space used within the file. – Aaron Bertrand Jun 20 '12 at 12:59
  • @AaronBertrand sorry, there was mistake- it had to be "smaller", just like in your case (mistake corrected in question). – Jānis Jun 20 '12 at 13:14
  • On more than one instance, one of them SQL Server 2008 R2 (RTM) - 10.50.1617.0. – Jānis Jun 20 '12 at 13:44
  • @AaronBertrand you are runing query on snapshot? – Jānis Jun 20 '12 at 13:44
  • No, I'm running your query, which looks at sys.master_files. You should be using a non-database-specific catalog view for this, because the one in the snapshot is actually the view in the source database (that view doesn't actually exist in the snapshot, right?). This is kind of the whole point of a snapshot, it only contains the snapshot image of things that have changed, but they will always be pre-change, so even if you change properties of the source database file (there aren't many you can make while a snapshot exists), the snapshot will always say is_sparse = 0. – Aaron Bertrand Jun 20 '12 at 13:47

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.

| improve this answer | |
  • Seems you are right about "startup" size. But how do you know that? i cant see reference about that in documentation.. – Jānis Jun 20 '12 at 13:51
  • @Jānis: I basically went and discovered it when I noticed that the size of tempdb reported in Management Studio didn't match up with what was in sys.master_files. I have no idea if it is or isn't documented anywhere, but I totally agree that it should be documented if it isn't. – Jon Seigel Jun 20 '12 at 13:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.