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.
Being alerted before the traps & syslog is filled would be nice. If not an alert, at least a status somewhere in the setting pages
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?
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
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 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.
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.
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 (
ROUND((MAX(x.DB_Size_MB) - MIN(x.DB_Size_MB)) / MIN(x.DB_Size_MB) * 100, 1) Growth_Percentage
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
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:
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
Hi @gharrison, @[Deleted User]
I was given this thread by SW Support for a query.
Since we have got the latest versions 2019.4 and 2020.2, do you see any latest alerting/report provided out of box which would help us to quicky setup and check the chart or alert on Database Growth or Transaction Log growth?
Recently we had an issue on the transaction log piled up and DB went to down state, due to replication did not occur since that server was in down state.
Someone please address to the latest versions, without DPA if we can view the growths.
Thank you
Naren.
Hi Naren,
I am not aware of a built-in report or alert for this in DPA, although that would be nice. I use my own custom scripts and monitoring, in conjunction with how I manage my DBs to try and stay on top of this. Basically, here's what I do. I adjust each DB's size and growth settings for every DB in my environment. I never use percentage growths, and always adjust my log growths to round figures, appropriate for each database (there is no "right" growth size for all DBs). I then use scripts that run from a central location, and log into each of the DB instances once per hour during business hours (and daily outside of that). These scripts check a variety of things, including sizes. The size script will look at the maximum allowable size (which again I set on every DB file), and it will look at the current size, and the growth size. It will then calculate how many "growths" are remaining before reaching the maximum size. I then have a simple CASE statement that says, if 2 growths then "DB SIZE - CRITICAL", if 2 then "DB SIZE - URGENT", if 4 then "DB SIZE -WARNING". These things are all written to a log file. The last step is to scan the log file for key words and phrases, including "DB SIZE". If any of those key words are detected, it will sent an e-mail to me.
Keep in mind, I have developed these scripts and tweaked them over the course of many ... many years, working for various companies. I don't mind sharing them, but these are only piece of my overall DB management I setup, and so you would likely need to "fill the gaps".
FYI, I do still also use the above for some SSRS reports that let me see growth over time, but I know that's not what you are looking for here.
Sorry, the formatting is getting messed up when I paste these. Here is the SQL script, and the Batch File:
SQL Script:
/*Glen HarrisonModified September 28, 2018Added code to handle files where Autogrowth is disabled.*/
SELECTCASEWHEN growth = 0 THEN 'INFORMATION - AUTOGROWTH DISABLED' /* Growth is Disabled */WHEN max_size = -1 THEN 'DB SIZE - CONFIG UNLIMITED' /* Unlimited Max Size */WHEN is_percent_growth = 1 THEN 'DB SIZE - CONFIG PERCENTAGE' /* Growth is by Percentage */WHEN ((CONVERT(BIGINT,Size)*8)/1024) + ((growth*8)/1024 * 2) >= CONVERT(BIGINT,Max_Size)*8/1024 THEN 'DB SIZE - CRITICAL' /* 2 Growth Remaining */WHEN ((CONVERT(BIGINT,Size)*8)/1024) + ((growth*8)/1024 * 3) >= CONVERT(BIGINT,Max_Size)*8/1024 THEN 'DB SIZE - URGENT' /* 3 Growths Remaining */WHEN ((CONVERT(BIGINT,Size)*8)/1024) + ((growth*8)/1024 * 4) >= CONVERT(BIGINT,Max_Size)*8/1024 THEN 'DB SIZE - WARNING' /* 4 Growths Remaining */ELSE 'INFORMATION'END Alert_Level,CASE is_percent_growthWHEN 1 THEN 'Percent'ELSE 'Absolute'END Growth_Type,CASEWHEN growth = 0 THEN 0 /* Autogrowth is Disabled */WHEN max_size = -1 THEN 9999999 /* Size is actually Unlimited */WHEN is_percent_growth = 1 THEN CONVERT(BIGINT,((CONVERT(BIGINT,Max_Size)*8/1024) - ((CONVERT(BIGINT,Size)*8)/1024)) / ROUND((((CONVERT(NUMERIC(10,2),Size)*8)/1024) * growth / 100),1))/* The above is an estimate, as it does not take compounding into account */ELSE ((CONVERT(BIGINT,Max_Size)*8/1024) - ((CONVERT(BIGINT,Size)*8)/1024)) / CONVERT(BIGINT,ROUND(((CONVERT(NUMERIC(10,2),growth)*8)/1024), 1))END Growths_Remaining,CASE TypeWHEN 0 THEN 'Data'WHEN 1 THEN 'Log'ELSE 'Other'END Type_Desc,(size*8)/1024 AS SizeMB,CASE Max_SizeWHEN -1 THEN 'Unlimited'ELSE CONVERT(VARCHAR(20),(CONVERT(BIGINT,Max_Size)*8)/1024)END Max_SizeMB,CASE is_percent_growthWHEN 1 THEN growthELSE (growth*8)/1024END Growth_Amount,SUBSTRING(DB_NAME(database_id), 1, 30) AS DatabaseNameFROM sys.master_filesORDER BY 1,3;
Batch File:
@echo offrem **************************rem Name: check_sizes_sql.batrem Created: June 17, 2014 by Glen Harrisonrem This script will login to each SQL Server instance defined in the sql_db.txt file, andrem run the check_sizes_sql.sql script. The script checks each database to determine ifrem the growth settings are set to absolute (by MB) rather than by percentage, and willrem also alert if there are fewer than 5 growths remaining before reaching the maximum size.rem The results will be logged, and scanned for errors and if any are detected, an alertrem will be sent.rem **************************
set script_name=check_sizes_sqlset script_dir=C:\DBA\scriptsset script_log=%script_dir%\%script_name%.logset script_sql=%script_dir%\%script_name%.sqlset db_list=%script_dir%\sql_db-temp.txt
rem Setup code for date/time commenting in log.FOR /F "TOKENS=1,2*" %%A IN ('DATE/T') DO SET NOW_DATE=%%BFOR /F "TOKENS=1,2*" %%A IN ('TIME/T') DO SET NOW_TIME=%%A %%B
echo Starting %script_name% at %NOW_DATE% %NOW_TIME% > %script_log%echo. >> %script_log%
rem Connect to each instance in the list and run the required SQL script.FOR /F "SKIP=4 TOKENS=1" %%X IN (%db_list%) do (
echo Checking Instance %%X >> %script_log%sqlcmd -E -S tcp:%%X -d master -t 60 -i %script_sql% >> %script_log%
echo. >> %script_log%echo Size check done for %%X at %NOW_DATE% %NOW_TIME% >> %script_log%echo. >> %script_log%
)
echo. >> %script_log%echo Completed %script_name% at %NOW_DATE% %NOW_TIME% >> %script_log%echo.
rem Print the output to the screen
echo Outputting log file contents: > contype %script_log% > conecho. > con
echo Check log file for errors > concall %script_dir%\mast_check_log.bat %script_log% sql
echo Output of mast_check_log: > contype %script_dir%\mast_check_log.log > con
echo. > con
exit
Just a follow up. In the example you were sharing, you had one of your nodes go down in a cluster. That is definitely something you can monitor through DPA, and absolutely should. If an cluster goes down to a single node, then you will have the potential for massive TLog growth, as it's not going to be able to clear those TLogs, as they will be waiting to apply those changes to your other node. Depending on how much activity in happening on this instance, you could easily run your TLog out of space in less than an hour, so the above alerts won't be much help. Using DPA to alert you to an outage is a great tool, but if you see something like that, you'll have to monitor it closely for your environment, and not necessarily rely on the alerting I've built. These alerts that I created allow me to stay on top of slower growth issues, but if something how crazy, it could easily use up all available space before I alert will detect it. That's why you would also need alerts for when systems go down, or files reach their maximum sizes.
Good luck!
Hello @gharrison,
Thank you so much for the quick reply with scripts and information. Much appreciated .
