DESCRIPTION
This alert works in conjunction with the SQL Server Database Size Collector alert in this same section. Please install that alert as a requirement for this alert. This alert will review the last 2 days of data from the collector alert and calculate the growth. You can use the thresholds in the alert to get warned when a database grows by more than 10% is a given day as an example.
ALERT DEFINITION
To create this alert, click on Alerts > Manage Alerts tab and create a Custom Alert of type Custom SQL Alert - Multiple Numeric Return using the SQL Statement attached to this alert.
REPORTING FROM THIS DATA
The data this alert collects is stored in the Ignite alert tables and can be retrieved running a query similar to this.
Note: this query retrieves data collected by this alert for the last 14 days. If the alert name was changed from the default listed above, change the "where a.alertname" part of this query as well.
SELECT d.name instance_name, ah.actiondate timestamp,
ahr.parametername database_name, convert(float, replace(ahr.levelvalue,',','')) AS database_size
FROM con_alert a
INNER JOIN con_alert_history ah on ah.alertid=a.id
INNER JOIN con_alert_history_results ahr on ahr.historyid=ah.historyid
INNER JOIN cond d ON d.id = ah.db_id
WHERE a.alertname='SQL Server Database Size Collector' -- adjust this to match the alert name used to collect the data
AND ah.actiondate >= DATEADD(day, -7, CURRENT_TIMESTAMP)
ORDER BY d.name, ah.actiondate, ahr.parametername
