Quick Scan Report – Database Snapshots

What this check looks for

Databases in sys.databases with a non NULL source_database_id, which identifies them as database snapshots, whose create_date is more than sixty days ago. The message names the snapshot and the database it was taken from.

Why it matters

A database snapshot is a point in time view of another database, implemented as a sparse file that fills up as the source changes. It is meant to be short lived, and an old one is costing you continuously.

How it works, because the cost follows directly from the mechanism. When a page in the source database is modified for the first time after the snapshot was created, SQL Server copies the original page into the snapshot’s sparse file before applying the change. That is called copy on write, and it means:

• Every first modification to a page costs an extra write. Not every write, only the first one to each page since the snapshot was taken, but on a busy database that is a great many pages.
• The snapshot grows toward the size of the source. It starts at almost nothing and accumulates a copy of every page that has changed. After sixty days on an active database it may hold a substantial fraction of the whole thing.
• With several snapshots on the same database, the write is copied into each of them. Three snapshots means three extra writes on the first modification to a page.

So an old snapshot is a slow, invisible tax. Nobody attributes a gradual increase in write latency to a snapshot created two months ago for a reporting task that finished the same afternoon.

The other costs:

• Disk space, which is the one people eventually notice. Sparse files report a small size in some tools and consume their real allocation on disk, which makes them easy to overlook when hunting for space.
• The source database cannot be dropped or restored while a snapshot exists on it. This is the one that bites during a recovery: a restore fails with a message about existing snapshots at the worst possible moment.
• A full snapshot file takes the snapshot offline, and it does so by marking it suspect. If the volume runs out of space the snapshot is unusable and has to be dropped anyway.
• Backup and maintenance jobs may iterate over it, wasting time on a read only view.

And the reason they linger is that snapshots are usually created for a specific, temporary purpose: before a schema change, before a data load, for a consistent reporting read, or as a fast rollback point during a deployment. The work finishes, the snapshot is forgotten, and there is no expiry mechanism.

How to confirm it yourself

Every snapshot on the instance with its age:

SELECT s.[name]                                  AS [snapshot_name],
       DB_NAME(s.[source_database_id])           AS [source_database],
       s.[create_date],
       DATEDIFF(DAY, s.[create_date], GETDATE()) AS [age_days],
       s.[state_desc]
  FROM sys.databases AS s WITH (NOLOCK)
 WHERE s.[source_database_id] IS NOT NULL
 ORDER BY s.[create_date];

What it is actually consuming on disk, which is the number worth knowing:

SELECT DB_NAME(mf.[database_id])                                     AS [snapshot_name],
       mf.[name]                                                     AS [logical_name],
       CAST(mf.[size] * 8.0 / 1024 AS DECIMAL(12,1))                 AS [max_size_mb],
       mf.[physical_name]
  FROM sys.master_files AS mf WITH (NOLOCK)
 WHERE mf.[database_id] IN (SELECT [database_id] FROM sys.databases
                             WHERE [source_database_id] IS NOT NULL)
   AND mf.[type] = 0
 ORDER BY [snapshot_name];

sys.master_files reports the maximum size the sparse file can reach, which is the size of the source file. For the space actually used, query from inside the snapshot:

USE [YourSnapshotName];
GO
SELECT [name],
       CAST([size] * 8.0 / 1024 AS DECIMAL(12,1))                              AS [max_mb],
       CAST(FILEPROPERTY([name], 'SpaceUsed') * 8.0 / 1024 AS DECIMAL(12,1))   AS [used_mb]
  FROM sys.database_files
 WHERE [type] = 0;

used_mb is the real consumption, and watching it over a week tells you how fast the copy on write traffic is accumulating.

Whether anything is still using it, which is the question that decides the outcome:

SELECT [session_id], [login_name], [host_name], [program_name], [login_time], [status]
  FROM sys.dm_exec_sessions WITH (NOLOCK)
 WHERE [database_id] = DB_ID('YourSnapshotName');

And the copy on write cost on the source, visible as write activity against the snapshot files:

SELECT DB_NAME(vfs.[database_id])  AS [database_name],
       mf.[physical_name],
       vfs.[num_of_writes],
       vfs.[num_of_bytes_written] / 1048576.0 AS [mb_written],
       vfs.[io_stall_write_ms] / NULLIF(vfs.[num_of_writes], 0) AS [avg_write_ms]
  FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS vfs
 INNER JOIN sys.master_files AS mf WITH (NOLOCK)
         ON mf.[database_id] = vfs.[database_id] AND mf.[file_id] = vfs.[file_id]
 WHERE vfs.[database_id] IN (SELECT [database_id] FROM sys.databases
                              WHERE [source_database_id] IS NOT NULL);

How to fix it

Find out why it exists, then drop it. There is no way to refresh or maintain a snapshot, so keeping an old one serves no purpose.

1. Establish the purpose. A snapshot created before a deployment two months ago has outlived its rollback window by a long way. One created for a reporting process may still be referenced by a report nobody has rewritten.
2. Check for connections using the session query above, and ask before disconnecting anyone.
3. If the data in it is genuinely still needed, extract it. A snapshot is not a backup and should not be treated as one, so copy what matters into a real table or take a proper backup of the source:

SELECT * INTO [Archive].[dbo].[OrdersAsOfJune]
  FROM [YourSnapshotName].[dbo].[Orders];

4. Drop it:

DROP DATABASE [YourSnapshotName];

That is instant, it reclaims the sparse file, and it removes the copy on write overhead immediately. It does not affect the source database in any way.

If a snapshot is genuinely part of a process, replace the long lived one with a short lived one. Create it as part of the job, use it, and drop it in the same job, with error handling so it is dropped even when the job fails:

CREATE DATABASE [Orders_snap]
  ON (NAME = N'Orders', FILENAME = N'E:\SQLSnapshots\Orders_snap.ss')
  AS SNAPSHOT OF [Orders];

BEGIN TRY
    -- do the work against Orders_snap
END TRY
BEGIN CATCH
    -- fall through to the drop
END CATCH;

DROP DATABASE [Orders_snap];

The undropped snapshot after a failed job is the most common origin of this finding, so the error handling is the part that stops it recurring.

Put the snapshot files somewhere other than the database volume. A snapshot that fills the data drive takes the source database down with it, which is a bad trade for a temporary convenience.

And check before any restore. If a restore of the source is planned, list snapshots first, because the restore will fail while any exist:

SELECT [name] FROM sys.databases WHERE [source_database_id] = DB_ID('Orders');

How long it takes

About half an hour to identify the purpose and drop them. The drop itself is immediate.

Related reports

Related checks

Frequently asked questions

Can a snapshot be used as a backup? No. It depends entirely on the source database being intact, so it protects against a logical mistake, not against a failure. If the source is lost, the snapshot is worthless.

Can I refresh a snapshot instead of recreating it? No. A snapshot is fixed at its creation point. To get a newer view you drop it and create another, which also resets the accumulated sparse file.

Does dropping it affect the source database? Not at all. The source is unchanged, and it stops paying the copy on write cost immediately.

Why does my restore fail with snapshots mentioned? A source database cannot be restored or dropped while any snapshot of it exists. Drop the snapshots first, which is why finding them before a recovery rather than during one matters.