How to find the actual space used for the sparse files used in Database Snapshot?

Database Snapshots are introduced in SQL Server 2005 and are available only in Enterprise Editions. Database Snapshots use copy-on-write along with NTFS Sparse file technology and this makes the Snapshot creation very quick. The amount of disk space used when creating the Snapshot is bare minimal and the actual size of the Snapshot will increase proportionally to the amount of data changes on the source database. Note that the Snapshot stores the original page from the source database when a change is made to a database page on source database for the first time. Subsequent changes to the same page are NOT copied onto the Snapshot. Another important point to note is that the maximum size of the snapshot can be the size of the source database when the Snapshot was created.

Now, What happens if you want to find out the amount of changes [actual space used in Snapshot] made to the Snapshot from the time it was created? Lets find out if there is a way figure out that information.

Let’s start with creating a snapshot of the AdventureWorks with the below code snippet.

CREATE DATABASE AdventureWorks_Snapshot_013010 ON
   ( NAME = AdventureWorks_Data
   , FILENAME = 'E:\AdventureWorks_data_013010.ss' )
AS SNAPSHOT OF AdventureWorks;
GO


Once the above statement completes, let’s take a peek of the actual Space used in both the source database and the snapshot right after the Snapshot creation.

USE AdventureWorks
SELECT  
    df.name AS 'FileName' 
    , physical_name AS 'PhysicalName'
    , size/128 AS 'TotalSizeinMB'
    , size/128.0 - CAST(FILEPROPERTY(df.name, 'SpaceUsed') AS INT)/128.0 AS 'AvailableSpaceInMB' 
    , CAST(FILEPROPERTY(df.name, 'SpaceUsed') AS INT)/128.0 AS 'ActualSpaceUsedInMB'
    , (CAST(FILEPROPERTY(df.name, 'SpaceUsed') AS INT)/128.0)/(size/128)*100. AS '%SpaceUsed'
    FROM sys.database_files df 
GO

USE AdventureWorks_Snapshot_013010
GO
SELECT  
    df.name AS 'FileName' 
    , physical_name AS 'PhysicalName'
    , size/128 AS 'TotalSizeinMB'
    , size/128.0 - CAST(FILEPROPERTY(df.name, 'SpaceUsed') AS INT)/128.0 AS 'AvailableSpaceInMB' 
    , CAST(FILEPROPERTY(df.name, 'SpaceUsed') AS INT)/128.0 AS 'ActualSpaceUsedInMB'
    , (CAST(FILEPROPERTY(df.name, 'SpaceUsed') AS INT)/128.0)/(size/128)*100. AS '%SpaceUsed'
    FROM sys.database_files df 
GO


Surprise, Surprise! Even though we are specifically looking at the actual space used of the Snapshot, the meta-data pulled from the above query is from the source database itself. Then how do we check the size of the Snapshot files? Is there a way to look at this information?

Yes, there are couple of options to look at this information and one of them is using T-SQL. Specifically using the sys.dm_io_virtual_file_stats.

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('AdventureWorks')
GO

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('AdventureWorks_Snapshot_013010')
GO


The results from the above queries are displayed below. Note that the Snapshot is taken only for the data files and not for the log file. The amount of actual disk space used is very small and in this case it was only 0.19 MB even though the size of the source database is 22.49 MB.

And the other option is to go thru the windows folder and looking at the properties of the file.

Before we move on, lets make an in place update on the source database and see the actual space used in the snapshot go up.

USE AdventureWorks
GO
UPDATE Sales.SalesOrderDetail
SET SalesOrderID = SalesOrderID


This post shows couple of options available to look at the actual space used for the sparse files used in the database snapshot. You may NOT use this everyday but this may be handy when you need it.