DESCRIPTION
This alert works in conjunction with the COLLECT DATABASE SIZES - POSTGRES 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 making it look similar to this example.
Alert Name: Database Growth Alert - Postgres
Execution Interval: 1 Day
Notification Text: The following relations have grown by more than X% in the last day
Available Instances: <choose any Postgres instances>
SQL Statement:
DECLARE @CurrentCollection datetime, @PriorCollection datetime, @CollectorAlertID int,@DBName varchar(200);
-- Change this query to match the name you used for the collector alert name, this is the default
SELECT @CollectorAlertID = id FROM con_alert WHERE alertname='Collect Database Size - Postgres';
SELECT @DBName = name FROM cond WHERE id = #DBID#
-- figure out the last collection and the one prior to that so we can compare
DECLARE col_cursor CURSOR FOR
SELECT TOP 2 actiondate
FROM (
SELECT DISTINCT ah.actiondate
FROM con_alert a
INNER JOIN con_alert_history ah ON ah.alertid=a.id
WHERE a.id=@CollectorAlertID
AND ah.actiondate >= DATEADD(day, -10, CURRENT_TIMESTAMP)
AND ah.dbname = @DBName) a
ORDER BY actiondate DESC;
OPEN col_cursor;
FETCH NEXT FROM col_cursor INTO @CurrentCollection;
FETCH NEXT FROM col_cursor INTO @PriorCollection;
CLOSE col_cursor;
DEALLOCATE col_cursor;
-- get the collected data and store into temp tables for later use
IF OBJECT_ID('tempdb..#TodaySizes') IS NOT NULL
DROP TABLE #TodaySizes
IF OBJECT_ID('tempdb..#YesterdaySizes') IS NOT NULL
DROP TABLE #YesterdaySizes
-- get alert's last run of sizes for all databases
SELECT ahr.parametername db_name, ah.actiondate, CONVERT(FLOAT, REPLACE(ahr.levelvalue,',','')) db_size_mb
INTO #TodaySizes
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
WHERE a.id=@CollectorAlertID
AND ah.actiondate=@CurrentCollection
AND ah.dbname = @DBName
-- get 2nd to last run of sizes for all database, so we can compare to previous run
SELECT ahr.parametername db_name, ah.actiondate, CONVERT(FLOAT, REPLACE(ahr.levelvalue,',','')) db_size_mb
INTO #YesterdaySizes
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
WHERE a.id=@CollectorAlertID
AND ah.actiondate=@PriorCollection
AND ah.dbname = @DBName
-- calculate the growth from yesterday to today and return the pct to DPA
SELECT TodaySizes.db_name, 100.0*(TodaySizes.db_size_mb - YesterdaySizes.db_size_mb) / YesterdaySizes.db_size_mb AS pct_growth
FROM #TodaySizes TodaySizes
INNER JOIN #YesterdaySizes YesterdaySizes ON TodaySizes.db_name = YesterdaySizes.db_name
WHERE TodaySizes.db_size_mb - YesterdaySizes.db_size_mb > 0
DROP TABLE #TodaySizes;
DROP TABLE #YesterdaySizes;
Execute Against: Repository
Units: Empty
High Threshold: Min: 10, Max: empty
Warning Threshold: Min: 5, Max: 10
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 ah.dbname instance_name, ah.actiondate timestamp,
ahr.parametername database_name, convert(float, replace(ahr.levelvalue,',','')) pct_growth_from_yesterday
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
where a.alertname='Database Growth Alert - Postgres'
and ah.actiondate >= current_timestamp - 14.0
order by ah.dbname, ah.actiondate, ahr.parametername