Monitor database total space usage not by individual data files.
I have many databases that we are monitoring for space which have multiple datafiles. Usually most the datafiles are full but at least 1 of the files is able to autogrow. Sometimes autogrow has reached its max limitation and sometimes we have turned it off once the file reached the largest size we will allow it. Our goal is to monitor so the database does not run out of space. We currently get alerts for the files that are full, we know some are full, we aren't worried about them, we still have 1 or 2 files we allow to grow and those are what we are worried about. Is there a way to turn off alerts just for the files we know are full and we don't want to monitor? Or is there a way to monitor database available space instead of on a file by file basis. I have attached a screenshot of what our database files layout typically is. So the first three files I don't want to get alerts on because I know I have 4 other files that can grow.
-
Blaž Dakskobler commented
First, consider changing the way you handle autogrowth, since it's better to keep DB files sized the same. See this for more info: https://www.mssqltips.com/sqlservertip/4937/expand-all-database-files-simultaneously-using-sql-server-2016-autogrowallfiles/
Saying that, check if these custom metrics I wrote can help you out:
https://sqlmonitormetrics.red-gate.com/percentage-of-usedavailable-database-space/
https://sqlmonitormetrics.red-gate.com/database-growth-steps-available/ -
PL commented