Actual (sparse) file size on disk of a snapshot

What is the actual (sparse) file size on disk of a snapshot?

--****************************************************************************************
-- What is the actual (sparse) file size on disk of a snapshot?
--****************************************************************************************
-- Versie:  1.0
-- Author:  Theo Ekelmans
-- Date:    2008-11-01
--
--****************************************************************************************
SELECT 
   DB_NAME(mf.database_id) AS Database_Name
   , mf.name AS Logical_Name
   , mf.Physical_Name
   , mf.FILE_ID
   , CAST((vfs.size_on_disk_bytes)/(1024.00*1024) AS NUMERIC(18,2)) AS Size_on_Disk_MB
   , CAST(mf.size/1024.00 AS NUMERIC(18,2)) AS Size_in_MB
   , mf.max_size
   , CAST(mf.growth/1024.00 AS NUMERIC(18,2)) AS Growth_in_MB
FROM sys.master_files mf
JOIN sys.dm_io_virtual_file_stats(NULL, NULL) vfs
ON mf.database_id=vfs.database_id AND mf.FILE_ID=vfs.FILE_ID
WHERE mf.database_id = DB_ID('DbName_snapshot')

Leave a Reply

Your email address will not be published. Required fields are marked *