How to Check TempDB Version Store Usage per Database in SQL Server

Summary: Use the sys.dm_tran_version_store_space_usage DMV to monitor how much TempDB space is consumed by version store records for Snapshot Isolation and RCSI.

Monitor Version Store Space Usage in TempDB

Looking to understand how much space the version store is using in your SQL Server tempdb? The sys.dm_tran_version_store_space_usage dynamic management view (DMV) is your primary tool for troubleshooting 1458 errors or general TempDB growth. It provides a summarized view of the total space consumed by version store records for each database on your instance.

This view is highly efficient and inexpensive to run because it does not traverse individual version store records. Instead, it provides an aggregated count of the pages used, making it an ideal choice for real-time monitoring and performance dashboards.

Retrieve Version Store Space Usage

Run the following T-SQL script to identify which database is responsible for the largest amount of version store data in TempDB:

SELECT 
    DB_NAME(database_id) AS [Database Name],
    reserved_page_count AS [Reserved Page Count],
    reserved_space_kb / 1024 AS [Version Store Space (MB)]
FROM sys.dm_tran_version_store_space_usage WITH (NOLOCK)
ORDER BY [Version Store Space (MB)] DESC
OPTION (RECOMPILE);
        

This query returns a clear breakdown of space usage, allowing you to quickly pinpoint which database has long-running transactions or high-volume updates under Snapshot Isolation or Read Committed Snapshot Isolation (RCSI).

Why Version Store Usage Matters

The version store is used to support row versioning features. If a transaction remains open for a long time, SQL Server cannot clean up the version store in tempdb, which can lead to disk space exhaustion. Monitoring this DMV helps DBAs proactively manage server health.

---

Related Reading:

• For deep technical details, visit the official Microsoft Documentation.

Found this T-SQL monitoring tip helpful? Share it with your team or subscribe for more SQL Server performance tuning guides!

How to Add Descriptions to SQL Server Tables and Columns using sp_addextendedproperty

Summary: Learn the T-SQL syntax for adding metadata descriptions to SQL Server tables and columns using sys.sp_addextendedproperty to improve database documentation.

Documenting Your Database: Adding Comments to SQL Server Objects

Adding descriptive comments to your SQL Server database objects is a best practice for long-term maintenance. These comments (extended properties) provide immediate context to developers and analysts, often appearing directly in tools like SQL Server Management Studio (SSMS).

---

How to Comment on a Table

Use the script below to define a description for a specific table at the schema level.

-- Add a description to a table
EXEC sys.sp_addextendedproperty   
 @name = N'MS_Description', 
 @value = N'Contains customer-related data.',   
 @level0type = N'SCHEMA',    
 @level0name = 'dbo',  
 @level1type = N'TABLE',      
 @level1name = 'tblCustomer';  
GO
        

How to Comment on a Column

To add a comment to a specific column, you must define the hierarchy down to level2.

-- Add a description to a column
EXEC sys.sp_addextendedproperty   
 @name  = N'MS_Description',
 @value = N'Primary key identifier for the customer.',  
 @level0type = N'Schema',  
 @level0name = 'dbo',  
 @level1type = N'Table',   
 @level1name = 'tblCustomer',   
 @level2type = N'Column',  
 @level2name = 'CustomerID';  
GO
        

---

Pro Tip: Using the name 'MS_Description' is standard; this allows the text to automatically appear in the "Description" field within the SSMS Table Designer and Object Explorer properties.

For more in-depth guidance on querying all existing comments across SQL Server objects, visit the reference below:

🔗 Find All Comments in SQL Server – Detailed Guide

SQL Server Database Snapshots: Creation and Restoration Guide (SQL 2016+)

Summary: Learn how to create and restore SQL Server Database Snapshots. This guide provides the T-SQL syntax for static, read-only snapshots and explains how to revert a database to a specific point in time.

Working with SQL Server Database Snapshots

Starting with SQL Server 2016 SP1, the Database Snapshot feature is supported across all editions, including Standard and Express.

A database snapshot provides a static, read-only view of a database at a specific point in time. It uses a "copy-on-write" mechanism: it doesn't store a full copy of the data initially, but as pages in the source database are modified, the original versions of those pages are copied into the snapshot's sparse file.

---

Creating a Database Snapshot

The snapshot must reside on the same server instance as the source database. The .ss extension is a common convention, but any extension is technically valid.

-- Generic Syntax
CREATE DATABASE <database_snapshot_name>
ON (
  NAME = <logical_file_name>, 
  FILENAME = '<os_file_name>'
) [ ,...n ]
AS SNAPSHOT OF <source_database_name>;

-- Example Implementation
CREATE DATABASE AdventureWorks_dbss1800 ON  
( NAME = AdventureWorks, FILENAME =  
'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Data\AdventureWorks_data_1800.ss' )  
AS SNAPSHOT OF AdventureWorks;  
GO
        

Restoring a Database from a Snapshot

You can quickly revert your source database to the state it was in when the snapshot was taken. This is much faster than a traditional restore but requires that no other snapshots exist on the same database.

-- Reverting the database
RESTORE DATABASE <database_name> 
FROM DATABASE_SNAPSHOT = <database_snapshot_name>;
GO
        

Critical Considerations

• Read-Only: Snapshots cannot be modified. They are ideal for reporting or "pre-deployment" safety nets.
• Sparse Files: The snapshot file starts small and grows as the source database changes. Monitor disk space closely!
• Dependency: If the source database goes offline or the data files are corrupted, the snapshot becomes useless. It is not a replacement for a full backup strategy.

---

Further Reading:

• Snapshots vs. Backups: Which should you use?
• Official Microsoft Documentation: CREATE DATABASE SNAPSHOT

Pro Tip: Snapshots are perfect for protecting against "Fat Finger" errors during major data updates. Take a snapshot, run your scripts, and if something goes wrong, you can revert in seconds!

SQL Server VLF Monitoring: How to Identify and Manage Virtual Log Files

Summary: Learn how to monitor Virtual Log Files (VLFs) in SQL Server using sys.dm_db_log_info. Discover why high VLF counts slow down database recovery and how to query for bottlenecks.

Monitoring Virtual Log Files (VLFs) for SQL Server Performance

Understanding and monitoring Virtual Log Files (VLFs) is a critical task for any Database Administrator. While the physical transaction log appears as a single file, SQL Server internally segments it into these smaller units.

---

What are Virtual Log Files (VLFs)?

VLFs are internal divisions within the transaction log. SQL Server manages the truncation and reuse of the log at the VLF level. You can explore the detailed physical architecture in the Official Documentation.

Why VLF Count Matters

An excessive number of VLFs (often caused by frequent, small log file growth increments) can lead to significant performance issues:

• Slower Database Recovery: SQL Server must initialize every VLF during startup or crash recovery.
• Extended Restore Times: High VLF counts add overhead to backup and restore operations.
• Log Cleansing Latency: It can impact features like Transactional Replication or Change Data Capture (CDC).

---

Querying VLF Information

To identify databases with a high VLF count across your entire instance, use the following T-SQL script. This leverages CROSS APPLY to aggregate data for every database.

-- Identify databases with high VLF counts
SELECT [name] AS [Database Name]
 ,[VLF Count]
FROM sys.databases AS db WITH (NOLOCK)
CROSS APPLY (
 SELECT file_id
 ,COUNT(*) AS [VLF Count]
 FROM sys.dm_db_log_info(db.database_id)
 GROUP BY file_id
 ) AS li
ORDER BY [VLF Count] DESC
OPTION (RECOMPILE);
        

To inspect the detailed properties (status, size, and offset) of VLFs for your **current database**, run this simplified query:

-- Detailed VLF info for current database
SELECT * FROM sys.dm_db_log_info(DB_ID());
        

---

Technical Reference:

For more in-depth information about the metadata returned by this function, refer to: sys.dm_db_log_info (Transact-SQL) .

Pro Tip: If your VLF count is in the thousands, consider shrinking the log file and manually growing it in larger chunks (e.g., 8GB or 16GB) to create a healthier, more manageable VLF structure.