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

Track data and log file growth over time - Alert on thresholds

Track data and log file growth over time - Alert on thresholds

This tool does a great job with monitoring performance, but it would be great to have some basic monitoring of growth for data and log files, and have some alerts based on thresholds when these are nearing their maximums.

7 Comments
Level 13

Being alerted before the traps & syslog is filled would be nice. If not an alert, at least a status somewhere in the setting pages

Level 9

Hi gharrison@abbotsford.ca ,

I believe we have some standard alerts in Ignite that can do what you're requesting. Under Manage Alerts -> Administrative Alerts -> we have a few such as; Database Freespace, Tablespace Freespace, Oracle Percent Redo Logs Unarchived and Transaction Log.Freespace.

If those don't cover what you'd like, here's a list of some custom created alerts we have available for you:

SQL Server

Custom Alert - SQL Server Database Growth

Custom Alert - SQL Server Database Size Collector

Custom Alert - Disk Free Space (MB)

Custom Alert - Disk Free Space (Percent)

Sybase

Custom Alert - Sybase Database Freespace

Oracle

Custom Alert - Oracle Database Percent Growth

Custom Alert - Oracle Collect Database Size

Custom Alert - Tablespace Growth Percent

Custom Alert - Collect Tablespace Sizes

Do any of these resources or stock reports help out? Or what are they missing that you need and for what RDBM are you monitoring / have as a repository?

Level 9

I’ve taken a look at the alerts you mentioned.

Database Freespace – Only available for SQL Server, but I run both SQL and Oracle.  If this was available for both, this would definitely help, although I’m also interested in growth over time, to just how is the space within the database getting used over a period of time, to predict when I need to expand space either within the database or to allocate more space from the SAN.

Tablespace Freespace – This may be somewhat similar to the above, but for Oracle, except that only “non-autoextensible online tablespaces are evaluated”.  This doesn’t help as all my tablespaces are autoextensible, but to a maximum size.  I need to see growth leading up to that size, and how quickly that space is being consumed.

I’m not as concerned about Transaction Log space or Redo Logs Unarchived, those alerts accomplish something different entirely.

As for the custom alerts, I’ve been playing with them for a couple days, but I’m struggling to get them to deliver what I need, or in one case to simply run correctly.

So, to restate my needs, what I’m looking for is three fold.  From within the database (both SQL Server and Oracle), I want to see (A) an alert based on space remaining of the maximum size I have set for a given database or tablespace, not simply to the next growth.  So if I’m getting close to my maximum, I want to get an alert warning me of this.  (B) I want to see a trend over time of the actual space used for each database or tablespace, to help me see what databases are growing quickly and which are not, to better plan my space.  (C) an alert from the OS level by drive when I’m getting close to the maximum space available on the drive.

Thanks,

Glen Harrison

Database Administrator

Level 9

Hi gharrison@abbotsford.ca,

For A) I believe the database growth custom alerts will do what you're looking for if you modify the query slightly so it doesn't come out as a percent. For B) we'll probably be looking into a metric or report for this request versus an alert which the custom alert I mentioned in A has appended towards the bottom. For C) I have a few queries I've been pending to write for a few customers that's still in QA before I can release them to Thwack or the customers, so I'll work on finishing those here in the next week or so.

I'll play around in my VM's for A, B and C and let you know if I can modify it / tweak it to do what you'd like to see and report back the results to this thread. I've got a few other metrics / alerts I need to get published out here first, but I'll work on the others ASAP. I'm more of a SQL Server kind of guy, although some of my associates here are Oracle DBA's and I'll see if I can leverage them to help out on a few of the RDBM query conversions for us here too.

Level 9

Hi gharrison@abbotsford.ca,

Here's my attempt at a few solutions for you. I've listed out your question in A / B / C and my response to go with them for your viewing ease. Please let me know if this is helpful or if we need to take another look at it.

A) An alert based on space remaining of the maximum size set for a given database or tablespace, not simply to the next growth.

Set this up for a 1 hour execution against the DB instance of your choice and DB within it. It will be a multiple numeric return type alert.

SQL Server

select

      name,

      convert(decimal(12,2),round((size-fileproperty(name,'SpaceUsed'))/128.000,2)) as 'FreeSpaceMB'

from dbo.sysfiles

Oracle (11g, you can use this as an example and work off it. Oracle keeps changing the system tables so what works in 10g may not work in 11, 12, etc.)

select

     df.tablespace_name "Tablespace",

     totalusedspace "Used MB",

     (df.totalspace - tu.totalusedspace) "Free MB",

     df.totalspace "Total MB",

     round(100 * ( (df.totalspace - tu.totalusedspace)/ df.totalspace)) "Pct. Free"

from

     (select

          tablespace_name,

          round(sum(bytes) / 1048576) TotalSpace

     from dba_data_files

     group by tablespace_name) df,

          (select

               round(sum(bytes)/(1024*1024)) totalusedspace,

               tablespace_name

          from dba_segments

          group by tablespace_name) tu

     where df.tablespace_name = tu.tablespace_name;

B) See a trend over time of the actual space used for each database or tablespace, to help see what databases are growing quickly and which are not.

These queries will help you get setup to collect and adjust this data as well as provide reporting off of it.

SQL Server Database Growth Collection Query + Reporting

Custom Alert - SQL Server Database Growth

Custom Alert - SQL Server Database Size Collector

Oracle Tablespace Collection Query + Reporting

Custom Alert - Oracle Collect Database Size

Custom Alert - Tablespace Growth Percent

C) An alert from the OS level by drive when getting close to the maximum space available on the drive

SQL Server

Listed at our Thwack: Custom Alert - Disk Free Space (Percent)

You will take a look at this snippet and replace the RED with the GREEN.

SELECT drive,

--   TotalSize as 'Total(MB)',

--   FreeSpace as 'Free(MB)',

   100.0 * FreeSpace / TotalSize 'Free(%)'

SELECT drive,

FreeSpace as 'Free


Oracle


Here we have a bit of an issue, I don't know of a good way to obtain this information in Oracle, even using views like v$datafile. We would need a good query that makes a system call, something like host df -k and then parse the data. We're a performance analysis tool for databases and this is more of an admin monitoring / capacity planning issue. We need more OS access which we don't want to have to commit to DPA as we want something light and unobtrusive to fit in our customers' environments. We do have other fantastic Solarwinds products that can do what your requesting plus even more and this would be what I'd recommend. Although if another user on the forum would like to correct me or shine some light on an alert / report that can meet the functionality of this request, I'd love to see it and try it out myself at home!

Level 12

I'm using SQL Server. The referenced custom alerts don't really address my need.

I am not concerned with seeing when a database has grown by more than X% in a day. I want to see the trend of growth over time (for example, a 12 month rolling average of n GB per month) for both mdf and ldf files by database for all databases in an instance. A chart would help me see any unusual increases, but I'd also like to be able to export the data for further analysis. I want to be able to analyze the trending of the sum of the ldf file sizes for a database separately from the mdf for that database. Having all of the files for a database combined together doesn't let me see the difference between the log and database files. Obviously, there are different factors that affect the growth for each of those.

Level 9

I know this is old now, but I was looking at something else and thought I'd update this with what I ended up doing.  In the end I leveraged data collected by a customer DPA alert that was suggested above.  I then adjusted the length of time that data was kept in order to keep one year of data within the DPA database.  Finally, I wrote a SSRS (SQL Server Reporting Services) report against that data.  Here's my main query for the SSRS report:

SELECT

ah.dbname Instance,

ahr.parametername DB_Name,

ah.actiondate Time_Stamp,

x.DB_Size_MB,

z.Growth_Percentage

FROM

dbo.con_alert a

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

  INNER JOIN dbo.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

  INNER JOIN (

   SELECT

    ah.dbname Instance,

    ahr.parametername DB_Name,

    ROUND((MAX(x.DB_Size_MB) - MIN(x.DB_Size_MB)) / MIN(x.DB_Size_MB) * 100, 1) Growth_Percentage

   FROM

    dbo.con_alert a

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

     INNER JOIN dbo.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

   GROUP BY

    ah.dbname,

    ahr.parametername

   HAVING

    ABS(ROUND((MAX(x.DB_Size_MB) - MIN(x.DB_Size_MB)) / MIN(x.DB_Size_MB) * 100, 1)) >= @parm_Growth_Percentage ) z

  ON ah.DBNAME = z.Instance AND ahr.PARAMETERNAME = z.DB_Name

WHERE

ah.dbname IN (@parm_Instance_List) AND

ahr.parametername IN (@parm_DB_List)

ORDER BY 1,2,3

Here's the SQL for the custom alert that captures the data in the first place:

-- 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

And finally, here's an example of my SSRS report, that I can run for any of my instances, and whatever databases exist on that instance, either one DB at a time, or any selection of them:

pastedImage_0.png

It's not perfect, it doesn't separate out data from transaction log, and I haven't gotten around to creating something for Oracle, but this certainly has helped out a lot.  I hope someone else here might find it helpful too.

Glen