Forecast and plan disk/database capacity
CONTEXT: When looking after a large number of databases, we have to make sure that none of them run out of space or I/O capacity.
PROBLEM: SQL Monitor needs to show us:
• The current size of Database files (and which disks they are on) and how full they are, ideally with a forecast
• Autogrowth configuration
• Space remaining on disk, ideally with a forecast
• File and disk-level IO metrics, ideally with a forecast
• Provide this data as metrics in the Analysis Graph
• Create a Disk Overview report with this data
• Enhance the "Disk space" alert with this data
What do you think about these solutions? Got another idea? Let us know in the comments!
Note that currently the best way to get a trend and forecast report on disk metrics is to export a few weeks’ worth of disk space data as a CSV and use e.g. Excel to plot a linear trendline.
We are considering a built-in report on disk usage, but we are currenlty thinking of it as a secondary priority.
Michael Berry commented
would also like to see it project out an estimate of when a drive would drun out of space based on existing growth trends.
John Henderson commented
On a Disk Space alert, it would be handy if you could see the amount of space used over a period of (say a week, but configurable), so you could quickly see if there was a spike when the alert fired.
brendo bongers commented
Agreed also, why not provide such basic measure out of the box instead of adding it with a custom metric.
John Scott commented
Currently the server overview page shows disk information such as drive letter, space used and disk I/O stats. Allow clicking on drive letter to drill through to disk details such as the details found when viewing disk alert details. Disk space used by info (total size, as % of disk, data files, log files) and data or tab to database files info would be helpful here.
Currently, the only way to view this information is when an alert is received. Being able to see this info on demand would help DBAs be more proactive when viewing an reporting from this data.
Thanks for your suggestion, that does make sense.
There is a workaround for this - you can install the custom metric 'Percentage of used/available database space' from here: http://sqlmonitormetrics.red-gate.com/percentage-of-usedavailable-database-space/
This will then be visible in the Analysis page.
Let me know if this works for you?
Jose Campo commented
I would like to suggest "Data Usage" metrics as part of the Analysis section.
Currently, we do have "Data Size" even "Log space usage" but not "Data Usage". Ability to see how much of the allocated space for a database (mdf file) is being used or how is growing over the time , allo a DBA to take corrective actions in terms of storage and SAN planning.
I am currently taking this metric on my own, using Reporting Services and do not understand why SQL Monitor can replicate same functionality.
Nicolaas Botes commented
Yes, thanks for your suggestions Nicolaas, we will certainly bear these in mind as we are developing the next releases.
In the meantime, we do have a free SQL Monitor SSRS Reporting Pack which has reports for DB size and Top DBs by Growth:
I hope that's useful. Thanks again for your feedback.
Hello Nicolaas .. Thanks a lot for your feedback.
Nicolaas Botes commented
DB sizing is any DBA's worst nightmare, mostly because very few of us were involved when the DB was designed. Thus, DB data- and log-file layouts, project growth and maximum sizing, all becomes very important properties and settings, which the DBA needs to be aware of. Also, tracking growth rate to plan for future planning regarding underlying disk space is as important to any DBA.
This would be very useful to incorporate it into SQL Monitor, having the option to navigate to SQL Server Instance-level, from the Global Overview option and then display the underlying properties for each database in terms of Total Size, Free Space and Un-used space, as well as logial names, file-location and properties mentioned above. Also, have the ability to track DB growth rate, saving historic sizes and plotting a trend. This can be enhanced even further by sending alerts based on incosistent growth rates, or even just the fact that DB data-files had to autogrow etc.