Snippet Name: SQL_Snapshot_Space_Usage
Tags:
Created Date: 2025-06-16 13:12:42
Last Modified Date: 2025-05-31 10:32:09
Dependencies:
DBA Notes:
Sample Output:
Title: SQL Snapshot Space Usage
Author: user
in SQL server, how can I determine how much space a SLQ Server Database Snapshot is using?
Author: ChatGPT
In SQL Server, you can determine how much space a **database snapshot** is using by querying the **sys.dm_io_virtual_file_stats** dynamic management function along with **sys.master_files** to check the sparse file sizes.
### Query to Check Snapshot Space Usage:
```sql
SELECT
db.name AS SnapshotName,
mf.name AS FileName,
mf.physical_name,
mf.size * 8 / 1024 AS AllocatedSizeMB,
vfs.num_of_bytes_written / 1024 / 1024 AS UsedSpaceMB
FROM
sys.master_files mf
JOIN
sys.databases db ON mf.database_id = db.database_id
JOIN
sys.dm_io_virtual_file_stats(mf.database_id, mf.file_id) vfs
ON mf.database_id = vfs.database_id AND mf.file_id = vfs.file_id
WHERE
db.source_database_id IS NOT NULL -- Only snapshots
ORDER BY
db.name, mf.file_id;
```
### Explanation:
- **`sys.master_files`**: Retrieves file metadata, including the sparse file size.
- **`sys.dm_io_virtual_file_stats`**: Provides actual space usage (`num_of_bytes_written`).
- **`db.source_database_id IS NOT NULL`**: Filters out regular databases, keeping only snapshots.
#### **Alternative: Using `sys.master_files` Only**
If `sys.dm_io_virtual_file_stats` isn't accessible, you can check sparse file sizes with:
```sql
SELECT
db.name AS SnapshotName,
mf.name AS FileName,
mf.physical_name,
mf.size * 8 / 1024 AS AllocatedSizeMB
FROM
sys.master_files mf
JOIN
sys.databases db ON mf.database_id = db.database_id
WHERE
db.source_database_id IS NOT NULL
ORDER BY
db.name, mf.file_id;
```
This gives **allocated space** but doesn't show **used space**.
#### **Check Physical File Size via Windows**
Since database snapshots use **sparse files**, you can check their real size using **Windows Explorer** or via PowerShell:
```powershell
Get-ChildItem "C:\SQLData\*.ss" | Select-Object Name, @{Name="SizeMB";Expression={$_.Length / 1MB}}
```
Would you like additional details or a way to automate periodic checks?
Author: user
this slightly modified query of the one you provided above gives some misleading stats about a database that i know has a lot of varchar(max) data that is large blob storage. can we modify to reflect the data that stored off row? SELECT
db.name AS SnapshotName,
mf.name AS FileName,
mf.physical_name,
convert(bigint,mf.size) * 8 / 1024 AS AllocatedSizeMB,
vfs.num_of_bytes_written / 1024 / 1024 AS UsedSpaceMB
--select *
FROM sys.master_files mf
JOIN sys.databases db
ON mf.database_id = db.database_id
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.file_id IN ( 1 ) ORDER BY convert(bigint,mf.size) DESC
Author: ChatGPT
To accurately reflect **off-row storage** such as `VARCHAR(MAX)`, `VARBINARY(MAX)`, `TEXT`, `NTEXT`, `IMAGE`, and **LOB (Large Object) data**, you need to account for **LOB storage space usage** in database snapshots.
### **Revised Query to Include Off-Row Storage**
This query retrieves space allocated for the snapshot **and includes LOB data pages** separately, so you can analyze their impact:
```sql
WITH SnapshotSpace AS (
SELECT
db.name AS SnapshotName,
mf.name AS FileName,
mf.physical_name,
CONVERT(BIGINT, mf.size) * 8 / 1024 AS AllocatedSizeMB,
vfs.num_of_bytes_written / 1024 / 1024 AS UsedSpaceMB
FROM sys.master_files mf
JOIN sys.databases db
ON mf.database_id = db.database_id
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.file_id = 1 -- Data file (not log file)
AND db.source_database_id IS NOT NULL
)
SELECT
ss.SnapshotName,
ss.FileName,
ss.physical_name,
ss.AllocatedSizeMB,
ss.UsedSpaceMB,
lob.total_pages * 8 / 1024 AS LOBDataSizeMB
FROM SnapshotSpace ss
LEFT JOIN (
SELECT
database_id,
SUM(total_pages) AS total_pages
FROM sys.dm_db_partition_stats
WHERE index_id IN (0, 1) -- Heap and Clustered Index (ignores non-clustered indexes)
AND in_row_data_page_count = 0 -- LOB Data only
GROUP BY database_id
) lob
ON ss.SnapshotName = DB_NAME(lob.database_id)
ORDER BY ss.AllocatedSizeMB DESC;
```
---
### **Why This Query Works Better?**
1. **Includes Off-Row Storage**:
- `sys.dm_db_partition_stats` tracks LOB (`VARCHAR(MAX)`, `VARBINARY(MAX)`, `TEXT`, etc.) storage separately.
- `total_pages` includes **only LOB data** pages (`in_row_data_page_count = 0` ensures off-row storage is counted).
2. **Better Snapshot Analysis**:
- The **LOBDataSizeMB** column now reflects the **off-row storage size** in MB.
- Helps explain why snapshots can grow significantly when large BLOBs change.
---
### **Notes & Considerations**
- If your database uses **filegroups for LOB storage**, you might need to adjust `mf.file_id` to include **all filegroups**.
- The query **does not include log files** since database snapshots do not track them.
- Snapshots grow when **any page changes**, so `LOBDataSizeMB` helps determine the contribution of **off-row data**.
---
### **Next Steps**
Would you like additional breakdowns, such as **index storage analysis**, or a way to **monitor changes over time**?
Author: user
i think we are missing a joined table with sys.dm_db_partition_stats, as there are three mising references: Msg 207, Level 16, State 1, Line 33
Invalid column name 'database_id'.
Msg 207, Level 16, State 1, Line 28
Invalid column name 'database_id'.
Msg 207, Level 16, State 1, Line 29
Invalid column name 'total_pages'.
Author: ChatGPT
You're absolutely right! The `sys.dm_db_partition_stats` DMV is database-specific, meaning it does not have a `database_id` column. We need to **switch context dynamically** to each snapshot database to query LOB storage.
Since **SQL Server does not allow cross-database queries using DMVs like `sys.dm_db_partition_stats`**, we have to **execute dynamic SQL per snapshot database**.
---
### **Fixed Query Using Dynamic SQL**
This script iterates over each snapshot database and gathers **LOB data size** using `sys.dm_db_partition_stats`:
```sql
DECLARE @sql NVARCHAR(MAX) = N'';
DECLARE @dbName SYSNAME;
-- Temporary table to store snapshot space results
IF OBJECT_ID('tempdb..#SnapshotLOBUsage') IS NOT NULL
DROP TABLE #SnapshotLOBUsage;
CREATE TABLE #SnapshotLOBUsage (
SnapshotName SYSNAME,
AllocatedSizeMB BIGINT,
UsedSpaceMB BIGINT,
LOBDataSizeMB BIGINT
);
-- Cursor to iterate over all snapshot databases
DECLARE db_cursor CURSOR FOR
SELECT name
FROM sys.databases
WHERE source_database_id IS NOT NULL; -- Only snapshots
OPEN db_cursor;
FETCH NEXT FROM db_cursor INTO @dbName;
WHILE @@FETCH_STATUS = 0
BEGIN
-- Build dynamic SQL to execute in the context of each snapshot
SET @sql = N'
USE ' + QUOTENAME(@dbName) + N';
WITH SnapshotSpace AS (
SELECT
DB_NAME() AS SnapshotName,
CONVERT(BIGINT, mf.size) * 8 / 1024 AS AllocatedSizeMB,
vfs.num_of_bytes_written / 1024 / 1024 AS UsedSpaceMB
FROM sys.master_files mf
JOIN sys.dm_io_virtual_file_stats(DB_ID(), NULL) vfs
ON mf.database_id = vfs.database_id
AND mf.file_id = vfs.file_id
WHERE mf.file_id = 1 -- Data file only
)
SELECT
ss.SnapshotName,
ss.AllocatedSizeMB,
ss.UsedSpaceMB,
COALESCE(SUM(ps.total_pages) * 8 / 1024, 0) AS LOBDataSizeMB
INTO #TempResult
FROM SnapshotSpace ss
LEFT JOIN sys.dm_db_partition_stats ps
ON ps.index_id IN (0, 1) -- Heap & Clustered Index
AND ps.in_row_data_page_count = 0 -- LOB data only
GROUP BY ss.SnapshotName, ss.AllocatedSizeMB, ss.UsedSpaceMB;
INSERT INTO #SnapshotLOBUsage
SELECT * FROM #TempResult;
DROP TABLE #TempResult;';
-- Execute the dynamic SQL for each snapshot
EXEC sp_executesql @sql;
FETCH NEXT FROM db_cursor INTO @dbName;
END;
CLOSE db_cursor;
DEALLOCATE db_cursor;
-- Retrieve final results
SELECT * FROM #SnapshotLOBUsage ORDER BY AllocatedSizeMB DESC;
-- Clean up
DROP TABLE #SnapshotLOBUsage;
```
---
### **Fixes & Improvements**
1. **Per-Snapshot Execution**:
- Uses `USE [snapshot_db]` dynamically.
- `sys.dm_db_partition_stats` is queried within each snapshot database.
2. **Correct Joins**:
- `sys.dm_db_partition_stats` now correctly joins **within** each snapshot DB.
- Only off-row storage (`in_row_data_page_count = 0`) is counted.
3. **Avoids Cross-DB Queries on DMV**:
- SQL Server restricts cross-database DMV queries.
- The script iterates over snapshots dynamically.
---
### **What This Query Now Shows**
| SnapshotName | AllocatedSizeMB | UsedSpaceMB | LOBDataSizeMB |
|-------------|---------------|------------|-------------|
| `Snapshot1` | `1024` MB | `600` MB | `250` MB |
| `Snapshot2` | `2048` MB | `1100` MB | `750` MB |
- **LOBDataSizeMB** shows **actual off-row storage** in the snapshot.
- **UsedSpaceMB** reflects changes due to page modifications.
---
### **Next Steps**
- Do you want to **automate this as a SQL Agent job** for monitoring?
- Need a **breakdown by table or index** to see **which tables consume the most LOB storage**?
Author: user
inside the cursor, this error repeats for every database: Msg 207, Level 16, State 1, Line 27
Invalid column name 'total_pages'
Author: ChatGPT
The issue occurs because `sys.dm_db_partition_stats` does not contain a column named `total_pages`. Instead, we should be summing `lob_used_page_count`, which tracks **off-row storage** (LOB pages).
Here???s the **corrected script** that dynamically gathers snapshot storage usage, including **LOB (Large Object) storage size**:
---
### **Fixed Query for Snapshot Space Usage (Including LOB Data)**
```sql
DECLARE @sql NVARCHAR(MAX);
DECLARE @dbName SYSNAME;
-- Temporary table to store snapshot space results
IF OBJECT_ID('tempdb..#SnapshotLOBUsage') IS NOT NULL
DROP TABLE #SnapshotLOBUsage;
CREATE TABLE #SnapshotLOBUsage (
SnapshotName SYSNAME,
AllocatedSizeMB BIGINT,
UsedSpaceMB BIGINT,
LOBDataSizeMB BIGINT
);
-- Cursor to iterate over all snapshot databases
DECLARE db_cursor CURSOR FOR
SELECT name
FROM sys.databases
WHERE source_database_id IS NOT NULL; -- Only snapshots
OPEN db_cursor;
FETCH NEXT FROM db_cursor INTO @dbName;
WHILE @@FETCH_STATUS = 0
BEGIN
-- Build dynamic SQL to execute in the context of each snapshot
SET @sql = N'
USE ' + QUOTENAME(@dbName) + N';
WITH SnapshotSpace AS (
SELECT
DB_NAME() AS SnapshotName,
CONVERT(BIGINT, mf.size) * 8 / 1024 AS AllocatedSizeMB,
vfs.num_of_bytes_written / 1024 / 1024 AS UsedSpaceMB
FROM sys.master_files mf
JOIN sys.dm_io_virtual_file_stats(DB_ID(), NULL) vfs
ON mf.database_id = vfs.database_id
AND mf.file_id = vfs.file_id
WHERE mf.file_id = 1 -- Data file only
)
SELECT
ss.SnapshotName,
ss.AllocatedSizeMB,
ss.UsedSpaceMB,
COALESCE(SUM(ps.lob_used_page_count) * 8 / 1024, 0) AS LOBDataSizeMB
INTO #TempResult
FROM SnapshotSpace ss
LEFT JOIN sys.dm_db_partition_stats ps
ON ps.index_id IN (0, 1) -- Heap & Clustered Index
GROUP BY ss.SnapshotName, ss.AllocatedSizeMB, ss.UsedSpaceMB;
INSERT INTO #SnapshotLOBUsage
SELECT * FROM #TempResult;
DROP TABLE #TempResult;';
-- Execute the dynamic SQL for each snapshot
EXEC sp_executesql @sql;
FETCH NEXT FROM db_cursor INTO @dbName;
END;
CLOSE db_cursor;
DEALLOCATE db_cursor;
-- Retrieve final results
SELECT * FROM #SnapshotLOBUsage ORDER BY AllocatedSizeMB DESC;
-- Clean up
DROP TABLE #SnapshotLOBUsage;
```
---
### **Fixes & Enhancements**
1. **Correct Column for LOB Storage**:
- Uses `ps.lob_used_page_count` (not `total_pages`) to correctly count **LOB storage pages**.
2. **More Reliable Joins**:
- Now joins `sys.dm_db_partition_stats` without filtering `in_row_data_page_count`, since LOB storage is already **tracked separately** in `lob_used_page_count`.
3. **Automates Snapshot Analysis**:
- Iterates over **each snapshot database** and dynamically runs the query in its context.
- Consolidates results into `#SnapshotLOBUsage`.
---
### **Expected Output Example**
| SnapshotName | AllocatedSizeMB | UsedSpaceMB | LOBDataSizeMB |
|-------------|---------------|------------|-------------|
| `Snapshot1` | `2048` MB | `1200` MB | `500` MB |
| `Snapshot2` | `1024` MB | `600` MB | `250` MB |
- **LOBDataSizeMB** now correctly reflects **off-row storage (LOBs) like VARCHAR(MAX), VARBINARY(MAX), TEXT, etc.**.
- **UsedSpaceMB** shows snapshot file growth.
---
### **Next Steps**
- Want a breakdown per **table or index** consuming the most LOB storage?
- Need an **alert system** for snapshots exceeding a threshold?
Let me know what works best! ????
Author: user
i am testing the command in the cursor on a single database which is effectively one table with tons of LOB objects. the LobDataSizeMB is returning a value of 0: WITH SnapshotSpace AS (
SELECT
DB_NAME() AS SnapshotName,
CONVERT(BIGINT, mf.size) * 8 / 1024 AS AllocatedSizeMB,
vfs.num_of_bytes_written / 1024 / 1024 AS UsedSpaceMB
FROM sys.master_files mf
JOIN sys.dm_io_virtual_file_stats(DB_ID(), NULL) vfs
ON mf.database_id = vfs.database_id
AND mf.file_id = vfs.file_id
WHERE mf.file_id = 1 -- Data file only
)
SELECT
ss.SnapshotName,
ss.AllocatedSizeMB,
ss.UsedSpaceMB,
COALESCE(SUM(ps.lob_used_page_count) * 8 / 1024, 0) AS LOBDataSizeMB
FROM SnapshotSpace ss
LEFT JOIN sys.dm_db_partition_stats ps
ON ps.index_id IN (0, 1) -- Heap & Clustered Index
AND ps.in_row_data_page_count = 0 -- LOB data only
GROUP BY ss.SnapshotName, ss.AllocatedSizeMB, ss.UsedSpaceMB;
Author: ChatGPT
Thanks for testing! If `LOBDataSizeMB` is returning **0**, it could be due to:
1. **LOB Storage Mechanism**: Some LOB data might be stored **in-row** rather than out-of-row.
2. **Incorrect Filtering**: The condition `AND ps.in_row_data_page_count = 0` might be **too restrictive**.
3. **Snapshot Behavior**: SQL Server snapshots store only **changed pages**, so if LOB data hasn???t been modified, it may not appear.
---
## **Steps to Fix**
We need to:
??? Remove unnecessary filtering (`AND ps.in_row_data_page_count = 0`).
??? Include **all** LOB storage (`lob_used_page_count`, `row_overflow_used_page_count`).
??? Use `DBCC IND` to confirm where LOBs are stored.
---
### **1. Modified Query (More Reliable LOB Data Counting)**
```sql
WITH SnapshotSpace AS (
SELECT
DB_NAME() AS SnapshotName,
CONVERT(BIGINT, mf.size) * 8 / 1024 AS AllocatedSizeMB,
vfs.num_of_bytes_written / 1024 / 1024 AS UsedSpaceMB
FROM sys.master_files mf
JOIN sys.dm_io_virtual_file_stats(DB_ID(), NULL) vfs
ON mf.database_id = vfs.database_id
AND mf.file_id = vfs.file_id
WHERE mf.file_id = 1 -- Data file only
)
SELECT
ss.SnapshotName,
ss.AllocatedSizeMB,
ss.UsedSpaceMB,
COALESCE(SUM(ps.lob_used_page_count + ps.row_overflow_used_page_count) * 8 / 1024, 0) AS LOBDataSizeMB
FROM SnapshotSpace ss
LEFT JOIN sys.dm_db_partition_stats ps
ON ps.index_id IN (0, 1) -- Heap & Clustered Index
GROUP BY ss.SnapshotName, ss.AllocatedSizeMB, ss.UsedSpaceMB;
```
??? **Fix: Includes `row_overflow_used_page_count`** (handles cases where large `VARCHAR(MAX)` is stored in overflow pages instead of pure LOB storage).
??? **Fix: Removed `AND ps.in_row_data_page_count = 0`** (some large objects might be partially in-row).
---
### **2. Verify if LOB Data is Stored Off-Row**
Run:
```sql
DBCC IND ('YourDatabase', 'YourTable', -1)
```
- Look at `IAM_TYPE` and `PageType`:
- `PageType = 3` ??? **LOB Data Page** (Should exist for off-row storage).
- `IAM_TYPE = 3` ??? **LOB IAM Page** (Manages LOB data).
- If these are **missing**, SQL Server is storing LOB data **in-row**.
---
### **3. Check LOB Storage at the Table Level**
Run this for **your table**:
```sql
SELECT
OBJECT_NAME(ps.object_id) AS TableName,
ps.row_count AS RowCount,
SUM(ps.in_row_data_page_count) * 8 / 1024 AS InRowMB,
SUM(ps.lob_used_page_count) * 8 / 1024 AS LOBMB,
SUM(ps.row_overflow_used_page_count) * 8 / 1024 AS RowOverflowMB
FROM sys.dm_db_partition_stats ps
WHERE OBJECT_NAME(ps.object_id) = 'YourTable'
GROUP BY ps.object_id, ps.row_count;
```
- If **`LOBMB = 0`**, SQL Server is **not storing LOB data off-row**.
- If **`RowOverflowMB > 0`**, LOB storage is in **row-overflow pages** instead.
---
### **Next Steps**
1. **If `LOBMB = 0`** ??? Data is in-row. Force LOB storage **off-row**:
```sql
ALTER TABLE YourTable ALTER COLUMN YourLOBColumn SET (LOB_STORAGE = OFF)
```
2. **If `RowOverflowMB > 0`** ??? Modify query to include row-overflow pages (already fixed above).
3. **If snapshot is not growing** ??? Your LOB data has not changed (snapshots only store changed pages).
---
### **Why This Happens?**
- SQL Server **stores LOBs in-row** if the **total row size fits in a page**.
- **Snapshots do not store unchanged pages**, so LOB data might **not be part of the snapshot**.
- **New LOB data written off-row appears in `lob_used_page_count`**, but if it???s small, SQL Server keeps it in-row.
---
### **Final Thoughts**
This fix should correctly capture **off-row storage in snapshots**, but if `LOBDataSizeMB` is still **0**, your LOBs are **not off-row** or **not changing**.
Let me know how this test goes! ????