cancel
Showing results for 
Search instead for 
Did you mean: 
Create Post

Custom Metric - TempDB Free Space Percent

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

Labels (1)
Comments

Tried using this and got "result 99" with a yellow warning triangle. No hints whatsoever on what to do next. 99?? What does that mean? To a novice DPA user, this means nothing and onto another tool.

It means that you have 99% of your extents used. Here is a discussion about tempdb: https://support.microsoft.com/en-gb/help/307487/how-to-shrink-the-tempdb-database-in-sql-server

Keep in mind that when tempdb allocates an extent, even when done with it, it will not automatically shrink tempdb. However, any currently unused extents will get re-used. What you are seeing is the high water mark for tempdb since the last instantiation.

Alright. Thank you for such a quick response. See, I clicked on the "Test Metric" button and was expecting something telling me that it is created properly and worked. The warning icon threw me, thinking that there was something wrong with the script. So, to me, not intuitive. I shall give this a whirl. Also, thanks for the link as well.

Cheers!

Also, according to Microsoft

Understanding Pages and Extents

Note

Log files do not contain pages; they contain a series of log records.

Version history
Revision #:
1 of 1
Last update:
‎01-07-2014 06:54 PM
Updated by: