We've been looking for an alternative advisory condition to the provided % Free Space - Data/Log File(s). I have one that we had used in the past but within SQL Sentry it only returns the first result - not multiples. Even if an order by is added to force the first result to be one of the events that should trigger, we get this same behavior.
Does anyone have a working solution? The use case is that many of our larger databases have 4 or more data files in a filegroup. We don't care if the individual files are getting to the threshold, we want to know if the filegroup is reaching the threshold.
Note - did not include the WHERE / the GROUP BY but these are the conditions -
The built-in which works (and gives multiple results) is -
SELECT ESC.ServerName + ': (' + FileName + ')',
100 - (CONVERT (DECIMAL (3, 2), ((CAST (FileUsedSize AS FLOAT) / (CAST (FileTotalSize AS FLOAT))))) * 100)
FROM PerformanceAnalysisSqlFile AS PASF
INNER JOIN
EventSourceConnection AS ESC
ON ESC.ID = PASF.EventSourceConnectionID
The modified one which used to work but doesn't now is -
SELECT ESC.ServerName + ' ' + sd.Name + ': (' + fg.GroupName + ')',
100 - (CONVERT (DECIMAL (3, 2), ((CAST (sum(FileUsedSize) AS FLOAT) / (CAST (Sum(FileTotalSize) AS FLOAT))))) * 100)
FROM PerformanceAnalysisSqlFile AS PASF
INNER JOIN
EventSourceConnection AS ESC
ON ESC.ID = PASF.EventSourceConnectionID
JOIN PerformanceAnalysisSqlFilegroup fg ON pasf.GroupID = fg.GroupID and pasf.EventSourceConnectionID = fg.EventSourceConnectionID
JOIN PerformanceAnalysisSqlDatabase sd ON fg.DatabaseID = sd.DatabaseID and fg.EventSourceConnectionID = sd.EventSourceConnectionID
These both return the expected results when executed against the repository. But, even testing with more aggressive values, the condition never fires despite the query clearly returning results that are less than the threshold. If anyone has something that works, let me know!
Thanks!!
