DESCRIPTION
This alert will collect the number of rows of the top 500 tables (based on number of rows) in all databases 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 Table Percent Growth". Also shown below is a query that will allow you to report on this data as collected in the DPA 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. Make it run once a day and use thresholds (required) like this so that the alert never fires:
REPORTING FROM THIS DATA
The data this alert collects is stored in the DPA 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 Rows
FROM ignite.con_alert a
INNER JOIN ignite.con_alert_history ah on ah.alertid=a.id
INNER JOIN ignite.con_alert_history_results ahr on ahr.historyid=ah.historyid
INNER JOIN ignite.cond d ON d.id = ah.db_id
WHERE a.alertname='SQL Server Table Size Collector' -- adjust this to match the alert name used to collect the data
AND ah.actiondate >= DATEADD(day, -14, CURRENT_TIMESTAMP)
ORDER BY d.name, ah.actiondate, ahr.parametername
