Massive Table Growth Issue – How to Set Up Filegroup Usage Alerts?
-
Hello We recently encountered an issue where a SQL table unexpectedly grew in size due to an application error. The table increased from a few gigabytes to around 11 TB within one week, which completely filled the PRIMARY filegroup. To prevent this from happening again, I would like to set up an alerting system that notifies us when the database or filegroup reaches 80% usage. My question is: Is there a built-in alerting mechanism in SQL Server that can trigger an alert when a filegroup or database exceeds a specific usage threshold? Or do we need to implement a custom solution (SQL Agent job, Extended Events, external monitoring tool, etc.)? Thank you in advance for your help.
-
You can create a standard SQL alert that is based on the db size (in KB) exceeding whatever amount you specify.
In SQL Server Agent; "Alerts"; "New Alert"; "SQL Server performance condition alert"; Object:"Databases"; Counter:"Data File(s) Size (KB)"; Instance: "<the_db_name>", etc.
If you have Operators defined in SQL, you can have the Operator notified, or (my preference) you can create a job ahead of time and have SQL start the job when the condition occurs.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
-
-
Sample of what Scott said, using the Alerts section in SQL Server Agent. This is slightly fragile as you configure each instance separately and need to maintain this.
Doing this in a better way is with some sort of Monitoring software, like Redgate Monitor, that sets alerts based on percentage and manages them over time.
You can query DMVs, using something like this script, and then look for items that are getting full (pick some level) and trigger an alert when something gets too high. I tend to want to get alerted when a data file or log file is getting to the 80% range. I might have a quarterly reminder to check if anything is in the 70s and then add space proactively.
The hard part is if a file groups quickly, like an app error. In those cases, you want some sort of thing that monitors the instance every few minutes.
-
To make it (much) less fragile, I would likely automate the creation of the Alerts by using a proc, via sp_add_alert.
The proc would take in the % size as a parameter, do the calc based on current file(s) size(s), and generate the Alert(s). Naturally you could have params for the operator(s)/email_addresses to notify, etc..
Unfortunately I don't have time to code up an example of this right now.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply