Part of the DBA-Tools Project.
The Snapshot Everyone Forgot They Created
A database snapshot is created for one specific reason, a pre-deployment rollback point, a reporting-consistency workaround, a test before a risky change, and then it just sits there. Snapshots don’t show up in a routine size check the way a growing data file does, but every snapshot silently consumes filegroup space for as long as it exists, growing as the source database changes underneath it.
This script lists every snapshot on the instance with its source database, age, and allocated size, so “forgotten and consuming space” becomes a specific, visible finding instead of an invisible one.
Why Database Snapshot Inventory Matters
- A snapshot’s storage footprint grows continuously as the source database changes, an old forgotten snapshot can end up consuming more space than anyone expects
- Snapshots are Enterprise-only, and one of the features flagged by Edition Feature Usage as a downgrade blocker, if you don’t know a snapshot exists, you won’t know to remove it before a downgrade
age_daysturns “is this still needed” into an objective number instead of a guess, a snapshot from a change that completed months ago is a strong candidate for cleanup- Dropping a snapshot is a genuinely safe, fast operation once you’ve confirmed nothing depends on it, the hard part is knowing it exists in the first place
When to Run This Script
- Routine storage or capacity reviews, snapshots are an easy thing to miss in a standard size check
- Before an edition downgrade, alongside Edition Feature Usage
- Reviewing a server you’ve just inherited, to catch snapshots left behind by whoever was here before
- Investigating unexplained filegroup or disk space consumption
The Script
/*
Script Name : Get-DatabaseSnapshotInventory
Category : monitoring
Purpose : Lists all database snapshots with source database, age, and allocated size,
snapshots silently consume filegroup space if forgotten.
Author : Peter Whyte (https://sqldba.blog/dba-scripts-get-database-snapshot-inventory/)
Requires : VIEW ANY DATABASE
*/
-- Blog: https://sqldba.blog/dba-scripts-get-database-snapshot-inventory/
-- SAFE:ReadOnly
-- IMPACT:Low
SET NOCOUNT ON;
SELECT
s.name AS snapshot_name,
src.name AS source_database,
s.create_date,
DATEDIFF(DAY, s.create_date, GETDATE()) AS age_days,
s.state_desc,
src.recovery_model_desc,
CAST(SUM(CAST(f.size AS BIGINT) * 8.0 / 1024) AS DECIMAL(20,2)) AS allocated_mb
FROM sys.databases s
JOIN sys.databases src ON src.database_id = s.source_database_id
JOIN sys.master_files f ON f.database_id = s.database_id
GROUP BY s.name, src.name, s.create_date, s.state_desc, src.recovery_model_desc
ORDER BY s.create_date ASC;
The source_database_id join is what identifies a database as a snapshot at all, regular databases have source_database_id = NULL and won’t appear in this result set.
How To Run From The Repo
Clone DBA Tools, initialize and run the script:
# Clone dba-tools repo:
git clone https://github.com/peterwhyte-lgtm/dba-tools
# Initialize environment:
cd dba-tools
.\Initialize-Environment.ps1
# List every database snapshot with age and allocated size:
.\run.ps1 Get-DatabaseSnapshotInventory
# To run against a remote sql server:
.\run.ps1 Get-DatabaseSnapshotInventory -ServerInstance SQLSERVER01
This script lives in the repo at:
Example Output
Real output from this lab instance, not staged: 0 rows returned, no database snapshots exist on this instance. An honest, clean result, and itself a useful confirmation, no forgotten snapshots quietly consuming space here.
Understanding the Results
- 0 rows — genuinely no snapshots exist; nothing to clean up, and nothing to flag before an edition downgrade
- age_days very high — the older a snapshot, the more likely the reason it was created no longer applies; confirm with whoever might still depend on it before dropping
- allocated_mb growing over time for the same snapshot — expected behavior, a snapshot’s footprint grows as the source database changes; this isn’t a leak, it’s how snapshots work, but it does mean an old one can be surprisingly large
- state_desc ≠ ONLINE — worth investigating specifically, a snapshot in a suspect or offline state may indicate the source database had an issue that affected the snapshot too
Best Practices
- Include this in routine storage reviews, snapshots don’t show up in a standard database-size check the way growing data files do
- Treat every snapshot older than a completed change window as a cleanup candidate, confirm nobody depends on it, then drop it (
DROP DATABASE [snapshot_name]) - Check this before any edition downgrade, snapshots are Enterprise-only and must be removed before moving to Standard
- Document the reason for any snapshot that’s genuinely meant to be long-lived, so a future review doesn’t misidentify it as forgotten
Related Scripts
You may also find these scripts useful:
Frequently Asked Questions
Does dropping a snapshot affect the source database?
No, dropping a snapshot only removes the snapshot itself, the source database is completely unaffected. The one thing to confirm first is that nothing (a report, a rollback plan, a test process) still depends on reading from that specific snapshot.
Why does a snapshot need Enterprise Edition?
Database Snapshots are one of the features Microsoft restricts to Enterprise regardless of SQL Server version. Standard Edition cannot create or read from a snapshot at all, this is why Edition Feature Usage flags any existing snapshot as a hard blocker for a downgrade to Standard.
Summary
A database snapshot is easy to create for a good reason and just as easy to forget about afterward, quietly consuming filegroup space the whole time. This script turns that blind spot into a direct, readable inventory: what exists, how old it is, and how much space it’s using.
Include it in routine storage reviews and before any edition downgrade, and treat every old snapshot as a cleanup candidate until confirmed otherwise.
Leave a Reply