Description
This custom metric will collec the current free space for TempDB as a percentage.
Metric Definition
To create the custom metric, click on Options > Custom Resource Metrics and configure the metric similar to this:
Database Versions: <no limitations>
Display Name: TempDB Free Space (%)
Description: <add your own description>
Category: <add to whatevfer category you like or create a new one>
Units: % (also check the "Chart as Percent" box)
Metric Type: Single Value
Frequency: 600
Timeout: 50
SQL Statement:
IF OBJECT_ID('tempdb..#t', 'U') IS NOT NULL
DROP TABLE #t
CREATE TABLE #t (FileID int, FileGroup int, TotalExtents bigint, UsedExtents bigint, dbname sysname, FileName sysname)
use tempdb
insert into #t exec('DBCC showfilestats')
select 100.0 - (100.0*SUM(UsedExtents*64.0) / SUM(TotalExtents*64.0)) from #t
DROP TABLE #t
