Description
This custom metric will collect the current used space for TempDB in MB.
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 Size (MB)
Description: <add your own description>
Category: <add to whatevfer category you like or create a new one>
Units: MB
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 SUM(TotalExtents)*64.0/1024.0 size_mb from #t
DROP TABLE #t
