Get tempdb version store space usage by database

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 is your go-to. It provides a table showing the total tempdb space consumed by version store records for each database.

This view is super efficient and inexpensive to run because it doesn't dig into individual version store records. Instead, it gives you an aggregated view of the version store space used in tempdb on a per-database basis. That makes it perfect for quick checks and monitoring!

Retrieve Version Store Space Usage

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

This query will give you a clear breakdown of the version store space by database, ordered from largest to smallest.

For more SQL Server insights, check out this article on getting your TempDB files count.

You can also find more detailed documentation on sys.dm_tran_version_store_space_usage on the Microsoft Docs website.

Database documentation within a database

This post demonstrates how to add descriptive comments to SQL Server database objects using SQL scripts. These comments help document your database structure and provide context to developers and analysts.

How to Comment on a Table in SQL Server

-- 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

Now let's see how to add a comment to a specific column within a table.

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

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

Find All Comments in SQL Server – Himanshu Patel

Database Snapshots (SQL Server)

Starting with SQL Server 2016 SP1, all editions now support the database snapshot feature.

A database snapshot provides a static, read-only image of a SQL Server database at a specific point in time. This snapshot remains transactionally consistent with the source database as it existed at the moment the snapshot was taken. Note that the snapshot must reside on the same server instance as its original database.

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

Note: The .ss file extension shown in the examples is user-defined and not mandatory.

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 the Original Database Using a Snapshot

RESTORE DATABASE <database_name> 
FROM DATABASE_SNAPSHOT = <database_snapshot_name>
  

Further Reading:

• Understanding Database Snapshots vs. Backups in SQL Server
• Create a Database Snapshot (Microsoft Docs)

Get VLF count for all databases

This post explores how to retrieve information about Virtual Log Files (VLFs) within your SQL Server transaction logs. Understanding VLF distribution is crucial because a large number of VLFs can negatively impact database startup, restore, and recovery times.

---

What are Virtual Log Files (VLFs)?

VLFs are segments within your SQL Server transaction log. While the physical transaction log file appears as one continuous unit, SQL Server internally divides it into these smaller, manageable VLFs. You can learn more about their architecture here.

---

Why VLF Count Matters

An excessive number of VLFs can lead to performance degradation. When SQL Server needs to read or process the transaction log (e.g., during database startup, recovery after a crash, or restoring a backup), it has to process each VLF. More VLFs mean more overhead, extending these critical operations.

---

Querying VLF Information

You can use the sys.dm_db_log_info dynamic management function to inspect VLF details for your databases.

Here's a handy query to identify databases with a high VLF count, ordered from highest to lowest:

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 see the detailed VLF information for your current database, you can run:

select * from sys.dm_db_log_info(db_id())

---

Further Reading

For more in-depth information about sys.dm_db_log_info, refer to the official Microsoft documentation: dm_db_log_info (Transact-SQL).

SQL Server database design: Best practice

Table Design Best Practices

• Avoid special characters and spaces in table names, column names, and other database object names (e.g., views, stored procedures). Stick to alphanumeric characters and underscores (_) for consistency and compatibility.
• Avoid storing binary data, such as files or images, directly in tables. Instead, use file paths or dedicated storage solutions like blob storage for better performance and manageability.
• Always define a primary key on each table. This ensures data integrity and helps with indexing and performance optimization.
• Do not use deprecated data types like NTEXT, TEXT, and IMAGE. Use NVARCHAR(MAX), VARCHAR(MAX), and VARBINARY(MAX) instead.
• Avoid using UNIQUEIDENTIFIER as a primary key unless absolutely necessary. It can lead to performance issues due to its large size and randomness, which affects index efficiency.

Get database file info

Ever wondered where your SQL Server database files are actually stored? This handy query helps you quickly find the **file names and physical paths for all user and system databases** on your instance. It's a great way to get an overview of your database file locations and properties.

SELECT DB_NAME([database_id]) AS [Database Name]
 ,[file_id]
 ,[name]
 ,physical_name
 ,[type_desc]
 ,state_desc
 ,is_percent_growth
 ,growth
 ,CONVERT(BIGINT, growth / 128.0) AS [Growth in MB]
 ,CONVERT(BIGINT, size / 128.0) AS [Total Size in MB]
 ,max_size
FROM sys.master_files WITH (NOLOCK)
ORDER BY DB_NAME([database_id])
 ,[file_id]
OPTION (RECOMPILE);

This query pulls information from the sys.master_files catalog view, which provides details about the files in your SQL Server instance.

SQL Server find database without backup from a given date

SQL Server finds a database without a backup from a given date

Below is a sample SQL query that retrieves the list of databases that have not had a backup since a specific date:

SELECT S.NAME AS database_name,
       'No Backups' AS [Backup Age (Hours)]
FROM master.dbo.sysdatabases S
LEFT JOIN msdb.dbo.backupset B
       ON S.name = B.database_name
       AND B.backup_start_date > '20201101'
WHERE B.database_name IS NULL 
      AND S.name <> 'tempdb'
ORDER BY B.database_name;

Additional Resources:

Backup History

Restore Database

Backup History Using TSQL

How to find log file used space in SQL server

How to find the log file used space in SQL Server

Check the log size in percentage across all databases using the following SQL query:

DBCC SQLPERF(LOGSPACE)

The result of the query provides details about the log space used by different databases. Below is an example of how the output might look:

Database Name   Log Size (MB)   Log Space Used (%)   Status

-----------------------------------------------------------

master          1.992188         43.52941            0

tempdb          15.99219         2.393747            0

model           7.992188         18.23069            0

msdb            28.80469         7.770545            0

This output shows the log size and the percentage of space used for each database. The data is useful for monitoring and maintaining the health of your SQL Server databases.