Version store space usage (tempdb)

Job details

Name:	Version store space usage (tempdb)
Platform:	Sqlserver
Category:	Capacity
Description:	Checks total space in tempdb used by version store records for each database.
Long description:	Checks total space in tempdb used by version store records for each database.
Version:	1.1
Default schedule:	15,35,55 * * *
Requires engine install:	Yes
Compatibility tag:	.[type=‘instance’ & databasetype=‘sqlserver’]/instance[maj_version > ‘2016′ & hasengine=‘YES’ & (engine_edition = ‘Microsoft SQL Server’	engine_edition = ‘Azure SQL Managed Instance’)]

Parameters

Name	Default value	Description
min histr interval	20	The minimum number of minutes before a new history record will be collected in the history table.
max histr	240	The number of hours the statistics will be kept in the history table.
alarm threshold	95	The maximum percentage of space usage allowed in the tempdb before an alarm is triggered.
warning threshold	80	The maximum percentage of space usage allowed in the tempdb before a warning is triggered.
enable warnings and alarms	NO	If set to “NO” (default), the alert will only collect statistics without returning status warning or alarm.
ignore databases		List of databases (separated by comma) which will be ignored. You can use % (percent sign) to represent wild card characters.

Job Summary

Purpose: The purpose of this job is to monitor and record the space usage of the version store in the tempdb for each database on SQL Server or Azure SQL Managed Instance versions 2016 and above.
Why: This job is critically significant because it helps to prevent the exhaustion of space in tempdb by keeping an eye on how much space the version store is consuming. By approaching or exceeding set thresholds, critical operations that depend on the version store might be impacted, potentially affecting database stability and performance.
Manual checking: You can manually monitor this data using SQL Server Management Studio with the following command:

SELECT DB_NAME(database_id) AS 'Database Name', reserved_page_count, round(reserved_space_kb/1024.,2) AS 'Space usage (MB)' FROM sys.dm_tran_version_store_space_usage ORDER BY 'Space usage (MB)' DESC

Detailed Execution Logic

The monitoring script executes in a structured process:
Gathering parameters like warning and alarm thresholds, historical intervals, and list of databases to ignore.

Dependency Management

This job relies on several internal objects which include:
master.dbo.sysaltfiles

These dependencies are crucial for the execution and integrity check of the task.

Alerting Criteria

Alert and warnings are generated based on:
Warning if the percentage usage exceeds the warning threshold but stays below the alarm threshold.

SQL Implementation Details

The SQL code snippets involved in the job creation include:
Creating tables for detailed and historical data storage.

Report Outputs

The job facilitates two key reports:
Current detailed space usage per database focusing on the top 10 entries.

This structured approach allows database administrators to efficiently track and manage tempdb space usage specifically occupied by version store, ensuring optimal performance and system stability.