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

Hello gharrison,

Thank you so much for the quick reply with scripts and information. Much appreciated :). 

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!

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 Harrison
Modified September 28, 2018
Added code to handle files where Autogrowth is disabled.
*/

SELECT
CASE
WHEN 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_growth
WHEN 1 THEN 'Percent'
ELSE 'Absolute'
END Growth_Type,
CASE
WHEN 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 Type
WHEN 0 THEN 'Data'
WHEN 1 THEN 'Log'
ELSE 'Other'
END Type_Desc,
(size*8)/1024 AS SizeMB,
CASE Max_Size
WHEN -1 THEN 'Unlimited'
ELSE CONVERT(VARCHAR(20),(CONVERT(BIGINT,Max_Size)*8)/1024)
END Max_SizeMB,
CASE is_percent_growth
WHEN 1 THEN growth
ELSE (growth*8)/1024
END Growth_Amount,
SUBSTRING(DB_NAME(database_id), 1, 30) AS DatabaseName
FROM sys.master_files
ORDER BY 1,3;

Batch File:

echo off
rem **************************
rem Name: check_sizes_sql.bat
rem Created: June 17, 2014 by Glen Harrison
rem This script will login to each SQL Server instance defined in the sql_db.txt file, and
rem run the check_sizes_sql.sql script. The script checks each database to determine if
rem the growth settings are set to absolute (by MB) rather than by percentage, and will
rem 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 alert
rem will be sent.
rem **************************

set script_name=check_sizes_sql
set script_dir=C:\DBA\scripts
set script_log=%script_dir%\%script_name%.log
set script_sql=%script_dir%\%script_name%.sql
set 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=%%B
FOR /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%

rem Setup code for date/time commenting in log.
FOR /F "TOKENS=1,2*" %%A IN ('DATE/T') DO SET NOW_DATE=%%B
FOR /F "TOKENS=1,2*" %%A IN ('TIME/T') DO SET NOW_TIME=%%A %%B

echo. >> %script_log%
echo Size check done for %%X at %NOW_DATE% %NOW_TIME% >> %script_log%
echo. >> %script_log%

)

rem Setup code for date/time commenting in log.
FOR /F "TOKENS=1,2*" %%A IN ('DATE/T') DO SET NOW_DATE=%%B
FOR /F "TOKENS=1,2*" %%A IN ('TIME/T') DO SET NOW_TIME=%%A %%B

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: > con
type %script_log% > con
echo. > con

echo Check log file for errors > con
call %script_dir%\mast_check_log.bat %script_log% sql

echo Output of mast_check_log: > con
type %script_dir%\mast_check_log.log > con

echo. > con

exit

Hi gharrison,  

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.

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:

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