DBA Scripts: Get Database Snapshot Inventory

---

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 were Enterprise-only up to SQL Server 2014 and have been available in Standard since 2016 SP1, so on a modern instance they are a space problem rather than a licensing one, and if you don’t know a snapshot exists, you won’t know to remove it before a downgrade
• age_days turns “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
*/
-- 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:

• sql/inventory/Get-DatabaseSnapshotInventory.sql

---

Example Output

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 if the target is SQL Server 2014 or earlier, where snapshots are Enterprise-only and must be removed first. From 2016 SP1 onward Standard supports them and nothing needs removing
• 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:

• Server Inventory (hub)
• Edition Feature Usage
• Database Sizes and Free Space
• Database Inventory
• Database Summary
• DBA Scripts: The Complete Guide, the map across every script on this site

---

Frequently Asked Questions

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.