Add FilegroupUsedPctOfMaxSize parameter to Performance Counters

[bruceasd]

Hi, we set a log tempdb max size appropriate for the situation, we don't leave it at 2TB. Would it be possible to include FilegroupUsedPctOfMaxSize parameter among the performance counters so I can manage it more easily, view its history, etc.?
Thanks.

[DavidWiseman]

Hi,

There is a performance counter you can add for this.

• In the DBA Dash config tool, goto the options tab.
• Click the Performance Counters button.
• Type percent log used into the search bar.
• Select the row in the grid, and select tempdb in the in the Instance drop down.
• Click Add.
• Save/Restart the service

You could also check All if you want to track for all databases - but this will collect more data, increasing the size of the table.

Note: It's percentage of log used rather than percentage of maximum size. There is also a Databases\Log File(s) Used Size (KB) counter to track the used size in KB that could be used.

Thanks,

David

[bruceasd]

Thanks, but I have already configured the "percent log used" parameter, but this generates false alarms as the Tempdb log is expanded several times but always remains far from the maximum achievable size.


I reached 95% of 904MB, receiving an alert, but the data file can grow up to 4000MB, so there is no concern.
Since I have many databases with different tempdb sizes, I can't use "Databases\Log File(s) Used Size (KB)."
The easiest thing to manage would be to include "FilegroupUsedPctOfMaxSize" among the performance counters. Is this something you can only do, or could I configure it somehow?

Thanks.

[DavidWiseman]

A useful workaround for any alert you need that isn't supported is to create an agent job that fails based on whatever criteria you need. DBA Dash can then alert on the job failure. e.g.

IF EXISTS(
	SELECT * 
	FROM dbo.FileStatus
	WHERE FilegroupUsedPctOfMaxSize>0.5
	--AND data_space_id = 0 /* uncomment for Log Files only */
	)
BEGIN
	RAISERROR('Filegroup percent of max size alert',11,1)
END

Alerts are also supported for performance counters, but there isn't a standard one that will give you what you need. You could add a custom SQL performance counter that tracks this which you could alert on.

The proc might look like this after editing the example, but you can adjust it as required as long as it's returning data in the same format.

CREATE PROC [dbo].[DBADash_CustomPerformanceCounters]
AS
-- Table variable isn't needed but it ensures the data is returned in the required format.
DECLARE @Return AS TABLE(
	SnapshotDate DATETIME2(7) NOT NULL DEFAULT(SYSUTCDATETIME()),
	object_name NVARCHAR(128) NOT NULL,
	counter_name NVARCHAR(128) NOT NULL,
	instance_name NVARCHAR(128) NOT NULL,
	cntr_value DECIMAL(28,9) NOT NULL,
	cntr_type INT NOT NULL DEFAULT(65792)
)
-- ********************** Insert your own counters here **********************
-- Ommitting SnapshotDate and cntr_type columns as these are supplied by table defaults
INSERT INTO @Return
(
    object_name,
    counter_name,
    instance_name,
    cntr_value
)
SELECT 
    N'User Counters',
    'Max % used of max file size',
    '',
    MAX(CASE 
        WHEN max_size = -1 THEN NULL -- Can't calculate % of unlimited
        WHEN max_size = 0 THEN 100.0 -- It's already at its max size
        ELSE CAST(size AS DECIMAL(28,9)) / CAST(max_size AS DECIMAL(28,9)) * 100.0
    END) AS [PercentOfMaxUsed]
FROM sys.master_files


-- ***************************************************************************

-- Return the data in the required format
SELECT SnapshotDate, -- Time in UTC
       object_name, -- Name of the object.  e.g. "User Counters"
       counter_name, -- Name of the counter.  e.g. "App Response Time (ms)"
       instance_name, -- Name of the instance.  e.g. "", "_Total", "DATABASE_NAME" etc
       cntr_value, -- The value of the couner (decimal)
       cntr_type -- follows the types ued in sys.dm_os_performanance_counters.  Use 65792 in most cases to use the value as is without further calculation.  Valid values: 65792,272696576,537003264,1073874176
FROM @Return

[DavidWiseman]

A pull request to add new alert types is also an option. See recent PR to add alert for SQL agent not running.