cancel
Showing results for 
Search instead for 
Did you mean: 
Create Post

Custom Alert - Postgres Database Growth

DESCRIPTION

This alert works in conjunction with the COLLECT DATABASE SIZE - 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 databases 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

Version history
Revision #:
1 of 1
Last update:
‎08-07-2020 04:00 PM
Updated by: