•analyse database growth easily using charts and graphs•raise alerts when database growth exceed expectations•identify the largest and fastest growing data files quickly•have growth reports sent to you daily via email•supports SQL Server 2005 and newer versions
Analyse SQL Server database
and table growth using
SQL Size can raise alerts when database growth exceeds expectations. Alerts can be sent via emails, and are also logged for your analysis. Alerts are implemented via scripts, which give you total control over when alerts are raised.
Alerts in SQL Size
In addition, SQL Size is shipped with over 10 template scripts you can use immediately, or modified to meet your requirements.
Let's take a common scenario - monitoring tempdb size. In SQL Size, just select the 'alert if TEMPDB size > 1 GB' template. As its name implies, this script raises an alert when the tempdb database size exceeds 1 gigabyte.
Look at the script above. It's easily understandable and more importantly, can be used immediately. Say you wanted to only raise the alert if the tempdb size was larger than 10 GB. Simply change the value in the comparison statement, save the script, and you're done.
change this value to 10
Now whenever the size of TEMPDB exceeds 10 GB, an alert is sent to your users.What if you wanted to allow TEMPDB to grow indefinitely, but raise an alert if the available space on the disk is less than 1 GB of free space remaining? Simple, select the 'alert if TEMPDB data disk has 1 GB free space' script.
The fully commented script walks you through what the script is doing each step of the way. The alert is raised on this line:
If you wanted to raise the alert only if the disk space was less than 500 MB, simply change that line to this:
and you're done.The alert scripts are ran every time SQL Size collects size data from your SQL Server instances. You may not want the same alert to be raised repeatedly, clogging up your mailbox. Simply use the RaiseConditionalAlert function instead e.g.
With this change, the alert will only be raised if the same alert for this instance was not raised in the last 24 hours. Can it get any simpler?The database configuration information collected by SQL Size can also be used in the scripts. There's a template script that does exactly this - raise an alert when a databases' file size exceeds 80% of the maximum size.
Thus, SQL Size can not only work with current size data, it can also access details of the database configuration, and use that information accordingly to monitor your database growth.
Admittedly, this script is longer than the previous examples, but it isn't significantly more complex. Let's break it down. Here we have the code that collects the growth data for all the databases.
Note that a lot of the code is setting up loops to iterate across all the databases, and for each database, the data files. The real work is done in this snippet:
We get the growth statistics for the last 14 days or later. We then use that to predict the growth for the next 14 days, and store that value in a variable.Once we've collected the projected growth for all the databases', we then make the checks. We iterate over all the paths found on the system, and compare the required space for next 14 days against the current available free space. If there is insufficient space to meet the projected growth, an alert is raised.
In addition, we also list out all the database files and their individual projected growth on that path.
Nice, yes?The scripting engine certainly gives you a lot of control over when and how alerts are raised. The provided templates also give you a good starting point, and in most cases, you only need to modify a couple of values to customise the script to your needs. The help file also provides details on the scripting engine and the various classes and functions available.If all else fails, drop us a line at firstname.lastname@example.org, and we will help you to the best of our ability.
Let's take things one step further. Let's use historical size data collected by SQL Size in our scripts, to predict future database growth! Again, a template is available that raises an alert if the projected data file growth for all databases' in the next 14 days exceeds the available disk capacity.