Description
This alert uses the data collected by the Collect Tablespace Sizes alert job and calculates the growth from one collection to the next. It can then alert when any tablespace grows by more than X% as defined in the thresholds.
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. Configure the alert similar to below.
Alert Name: Tablespace Growth Percent
Execution Interval: 1 Hour
Notification Text: The following list of tablespaces exceeded the growth limits.
SQL Statement For Oracle Repository:
WITH latest_rows AS
(
SELECT ROW_NUMBER() OVER (PARTITION BY ahr.parametername ORDER BY ah.actiondate DESC) row_num,
ahr.parametername ts_name,
to_number(replace(ahr.levelvalue,',','')) ts_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
WHERE a.alertname='Collect Tablespace Sizes'
AND ah.dbname = (select name from cond where id=#DBID#)
)
SELECT d1.ts_name, 100*(d1.ts_size_mb - d2.ts_size_mb) / d1.ts_size_mb pct_growth
FROM (SELECT ts_name, ts_size_mb FROM latest_rows WHERE row_num = 1) d1
INNER JOIN (SELECT ts_name, ts_size_mb FROM latest_rows WHERE row_num = 2) d2 ON d2.ts_name = d1.ts_name
SQL Statement For SQL Server Repository:
DECLARE @CurrentCollection datetime, @PriorCollection datetime, @CollectorAlertID int,@DBName varchar(200);
SELECT @CollectorAlertID = id FROM con_alert WHERE alertname='Collect Tablespace Sizes - Oracle';
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 tablespace_name, ah.actiondate, CONVERT(FLOAT, REPLACE(ahr.levelvalue,',','')) ts_size_gb
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 tablespace_name, ah.actiondate, CONVERT(FLOAT, REPLACE(ahr.levelvalue,',','')) ts_size_gb
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 Ignite
SELECT TodaySizes.tablespace_name, 100.0*(TodaySizes.ts_size_gb - YesterdaySizes.ts_size_gb) / YesterdaySizes.ts_size_gb AS pct_growth
FROM #TodaySizes TodaySizes
INNER JOIN #YesterdaySizes YesterdaySizes ON TodaySizes.tablespace_name = YesterdaySizes.tablespace_name
WHERE TodaySizes.ts_size_gb - YesterdaySizes.ts_size_gb > 0
DROP TABLE #TodaySizes;
DROP TABLE #YesterdaySizes;
Execute Against: Repository
Units: % Growth
High Threshold: Min 10, Max empty
Medium Threshold: Min 5, Max 10
