Temporary database space usage

Job details

Name:	Temporary database space usage
Platform:	Sqlserver
Category:	Capacity
Description:	Checks space usage in tempdb database, and collects statistics including size of data and transaction log files.
Long description:	Checks space usage in tempdb database, and collects statistics including size of data and transaction log files.
Version:	2
Default schedule:	0 * * *
Requires engine install:	Yes
Compatibility tag:	.[type=‘instance’ & databasetype=‘sqlserver’]/instance[maj_version > ‘2000′ & 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.

Job Summary

Purpose: The purpose of this job is to monitor and manage space usage in the temporary database (tempdb) within SQL Server environments. This includes tracking the size and usage of data and log files, and generating alerts or warnings based on configurable thresholds.
Why: This job is critical for preventing potential issues related to space constraints in the tempdb, which can affect database performance and stability. By actively monitoring and managing space usage, potential disruptions can be preemptively addressed, ensuring smoother operations.
Manual checking: You can check this manually in the database by executing the following SQL commands:

SELECT t2.name as [Logical Name], t2.filename as [File Name], (t2.size*1.0)/128 AS [File Size], (t1.unallocated_extent_page_count*1.0)/128 AS [Free Space], case when t2.growth = 0 then 'autogrowth disabled' else case when t2.status & 0x100000 = 0 then convert(varchar(10), CONVERT(Decimal(15,2), ROUND(t2.growth/128.00,2))) + ' MB' else convert(varchar(10), t2.growth) + '%' end end as "Growth", "Max Size" = case when t2.maxsize = -1 then 'Unlimited' else cast((t2.maxsize/1024)*8 as varchar(30)) end FROM tempdb.sys.dm_db_file_space_usage t1, tempdb..sysfiles t2 WHERE t1.file_id = t2.fileid ORDER BY 4 ASC

Details and Configuration

Default Schedule: Continuous monitoring with specific execution timings set internally.
Dependencies: Relies on several database objects and custom tables like dbw_temp_db_files_detailed and dbw_temp_db_space_usage_histr for storing the historical data.
History Record Control: History of space usage is maintained, with intervals and durations configurable by parameters such as minimum history interval and maximum history record duration.
Alerts and Warnings: Configurable thresholds for both warnings and alarms are present to indicate critical levels of space usage, offering the ability to enable or disable these notifications.

Reports and Visualizations

“Temporary Database Space Usage”: Detailed views and statistics about the current usage of space within tempdb.
“Temporary Space Usage History”: Visual representation over time for monitored data files, helping to identify trends or sudden changes in space consumption.

Feature	Implementation
Detailed Reports on File Usage	Table and chart views listing all temporary files by various metrics such as size and free space.
Historical Usage Trends	Charts that track usage over time, enabling easy spotting of peaks and potentially problematic trends.
Alarms and Notifications	Configurations to trigger alarms or warnings based on predefined criteria, with settings to handle the scenarios programmatically.

Improvements & Upgrade Details

Version Control: The job is versioned with regular updates for improvements and new features.
Upgrade Scripts: SQL statements and procedures to update existing installations to incorporate new checks, parameters, and optimizations.
Compatibility: Designed to support various configurations of SQL Server and Azure SQL Managed Instances, ensuring broad applicability.

This task is essential for administrators to ensure efficient operations by preemptively managing space within tempdb, thus preventing possible slowdowns or crashes due to space limitations.