DESCRIPTION
This alert will collect the size each database in a SQL instance. This example alert is used only for collecting data and would never alert anyone. This data is provided as input for another custom alert named "SQL Server Database Percent Growth". Also shown below is a query that will allow you to report on this data as collected in the Ignite alert tables.
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.
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,',','')) db_size_mb
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='Collect Database Size - SQL Server'
AND ah.actiondate >= current_timestamp - 14.0
ORDER BY d.name, ah.actiondate, ahr.parametername
