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

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

Reports and Visualizations

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

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.