Custom Alert - SQL Server Database Size Collector

darichar

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 making it look similar to this example.

Alert Name: Collect Database Size - SQL Server

Execution Interval: 1 Day

Notification Text: <None required>

Available Instances: <choose any SQL instances>

SQL Statement:

-- drop and recreate the temp table used to store info from sp_helpdb procedure

IF OBJECT_ID('tempdb..#HelpInfo') IS NOT NULL

   DROP TABLE #HelpInfo

CREATE TABLE #HelpInfo (db_name SYSNAME, db_size VARCHAR(18), owner SYSNAME NULL, dbid INT,

created VARCHAR(18), status VARCHAR(500) NULL, compatibility_level VARCHAR(5))

-- capture the output of sp_helpdb

INSERT INTO #HelpInfo EXEC ('sp_helpdb')

SELECT [db_name], LEFT([db_size], Len(db_size)-3) db_size_mb FROM #HelpInfo

DROP TABLE #HelpInfo

Execute Against: Monitored Database

Units: Empty

High Threshold: Min: -2, Max: -1

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,',','')) 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

where a.alertname='Collect Database Size - SQL Server'

and ah.actiondate >= current_timestamp - 14.0

order by ah.dbname, ah.actiondate, ahr.parametername

gharrison

After working with the query provided to report on this data, I think the following may work a bit better.  I had an issue with a predicate throwing an error when converting the string to a float, because there was non-numeric data in the table (even though it wasn't being returned in the query).  In any event, if someone else was having a similar issue, this query seems to work without issue:

SELECT

ah.dbname instance_name,

ah.actiondate timestamp,

ahr.parametername database_name,

x.db_size_mb

FROM

con_alert a

  INNER JOIN con_alert_history ah ON ah.alertid=a.id AND a.alertname='Collect Database Size - SQL Server'

  INNER JOIN con_alert_history_results ahr ON ahr.historyid=ah.historyid

CROSS APPLY ( SELECT [db_size_mb] = CASE

     WHEN ISNUMERIC(ahr.LEVELVALUE) = 1 THEN CONVERT(FLOAT, REPLACE(ahr.levelvalue,',',''))    

     ELSE 0

     END ) x