CustomCheck table not populating

[elshelto]

I wanted to add a check to look for tempdb data files that are not the same size. I used the template to create [dbo].[DBADash_CustomCheck] in my db (DBADashDB). When I run the stored procedure manually, I get the expected output. What I am not seeing is any data inserted in to dbo.CustomChecks or dbo.CustomChecksHistory.

When I go to Checks->Collection Dates, I see a recent snapshot for CustomChecks. I have tried to stop/start the service. I have tried changing the schedule from every hour to every 5 min hoping to get data, but no luck. Snapshot age for customchecks seems to show it running on the expected schedule.

Not sure what I am missing but would appreciate any advice.

Below is my stored proc for reference.

CREATE OR ALTER PROC [dbo].[DBADash_CustomCheck]

/****************************************************************************
Run your own tests and insert the results into @CustomChecks table.
Test = Name of your custom test
Context = Context where the test applies to. e.g. application name, job name, database name, server name etc.
Status = 1=Critical (Red), 2=Warning (Yellow), 3= N/A (Grey), 4=OK (Green)
Info = Any additional information you would like to include
****************************************************************************/

AS

-- Table variable used to ensure the output is in the required format
DECLARE @CustomChecks TABLE
(
    Test    NVARCHAR(128) NOT NULL
   ,Context NVARCHAR(128) NOT NULL
   ,Status  TINYINT       NOT NULL
   ,Info    NVARCHAR(MAX) NULL
   ,PRIMARY KEY (Test, Context)
   ,CHECK (Status IN (1, 2, 3, 4))
)

;
WITH cteSQL AS
    (
        SELECT MIN(dbf.size) [MinSize], MAX(size) [MaxSize]
            FROM [dbo].[DBFiles] dbf
                INNER JOIN dbo.Databases d ON d.DatabaseID = dbf.DatabaseID
            WHERE d.name = 'tempdb' AND dbf.type = 0
    )
INSERT INTO @CustomChecks (Test, Context, Status, Info)
    SELECT 'TempDB Consistency' [Test]
          ,'TempDB' [Context]
          ,CASE WHEN c.MinSize <> c.MaxSize THEN 2 ELSE 4 END [Status]
          ,CASE WHEN c.MinSize <> c.MaxSize THEN 'Warning' ELSE 'OK' END [Info]
        FROM cteSQL c

/***************************************************/

-- return data in required format
SELECT Test, Context, Status, Info FROM @CustomChecks

GO

[DavidWiseman]

Hi,

DBA Dash already has a check for this if you go to the Configuration node and select the TempDB tab. It will highlight if files are evenly sized with even growth and the recommended number of files. You might already be aware of this and just want it highlighted more prominently on the Summary tab but thought it was worth mentioning.

Fixing the custom checks procedure:

This needs to be created on the monitored instances. As it relies on the tables in the DBA Dash repository DB, this could only be created on the instance that has the repository DB. The procedure needs to be created in the default database that the service connects to as specified on the source connection string. The custom check would be associated with the instance that has the DBA Dash repository DB.

Note: There is a bug in the check as it's checking that your tempdb files are the same size across all your SQL instances. There is a dbo.TempDBConfiguration view that has a IsEvenlySized column that you could use. e.g.

SELECT 'TempDB Consistency' [Test],
       'TempDB' [Context],
	   CASE WHEN MIN(CAST(IsEvenlySized AS INT)) = 0 THEN 2 ELSE 4 END AS [Status],
	   CASE WHEN MIN(CAST(IsEvenlySized AS INT)) = 0 THEN 'Warning' ELSE 'OK' END [Info]
FROM dbo.TempDBConfiguration

or

SELECT 'TempDB Consistency' [Test],
       InstanceDisplayName [Context],
	   CASE WHEN IsEvenlySized = 0 THEN 2 ELSE 4 END AS [Status],
	   CASE WHEN IsEvenlySized = 0 THEN 'Warning' ELSE 'OK' END [Info]
FROM dbo.TempDBConfiguration

Custom checks allow you to run whatever test you need against your monitored instances - often data that DBA Dash wouldn't usually collect. You need to deploy the stored proc to each monitored instance you want the check to run against. There wouldn't be anything wrong with running a check against the data already collected by DBA Dash on the repository instance. Another option would be to deploy the SP to each instance and run the check against sys.database_files. e.g.

CREATE OR ALTER PROC [dbo].[DBADash_CustomCheck]

/****************************************************************************
Run your own tests and insert the results into @CustomChecks table.
Test = Name of your custom test
Context = Context where the test applies to. e.g. application name, job name, database name, server name etc.
Status = 1=Critical (Red), 2=Warning (Yellow), 3= N/A (Grey), 4=OK (Green)
Info = Any additional information you would like to include
****************************************************************************/

AS

-- Table variable used to ensure the output is in the required format
DECLARE @CustomChecks TABLE
(
    Test    NVARCHAR(128) NOT NULL
   ,Context NVARCHAR(128) NOT NULL
   ,Status  TINYINT       NOT NULL
   ,Info    NVARCHAR(MAX) NULL
   ,PRIMARY KEY (Test, Context)
   ,CHECK (Status IN (1, 2, 3, 4))
)

INSERT INTO @CustomChecks (Test, Context, Status, Info)
SELECT 'TempDB Consistency' [Test],
       'TempDB' [Context],
	   CASE WHEN MIN(f.size) <> MAX(f.size) THEN 2 ELSE 4 END AS [Status],
	   CASE WHEN MIN(f.size) <> MAX(f.size) THEN 'Warning' ELSE 'OK' END [Info]
FROM tempdb.sys.database_files f
WHERE type=0

/***************************************************/

-- return data in required format
SELECT Test, Context, Status, Info FROM @CustomChecks

Note: You also need to ensure that the service account has permissions to execute the stored procedure. The DBA Dash ErrorLog tab in the Checks node can be used to look for errors and you can also check the Logs folder on the service.

Hope this helps

PS
As an alternative to custom checks, you can consider creating a SQL agent job that will fail/succeed depending on the result of your test. The status of the SQL agent job will appear on the summary tab.

[elshelto]

Thank you for your response and your work on this awesome project. Still trying to figure out how to leverage all the things this tool does.