Monitor Database Growth and Usage
By Willem Gossink, 2014/12/04
As a DBA, you probably manage hundreds of databases, if not thousands. Each of these databases has at least two devices and each of these devices may grow (or shrink), either through autogrowth, scripting or manual interventions. Even if we do not consider the creation or deletion of databases or the addition of devices, you may be facing a lot of changes on the storage solution that underlies your database files. In most cases, these changes tend to gradually fill up your disks.
Build a repository
If you want to manage that process of database disk usage (capacity management?), you will require history data. You will need a way to record information about your database files and store that data in a repository. A process like that will put you in the know about changes taking place and will allow you to do some basic trending.
That is exactly what the PowerShell script outlined below will do for you: pull out information regarding size and usage of database devices from your database servers and store that information in a history table. You can then report on the data using tools such as Excel PivotChart or your own favorite graphing solution, and monitor trends. Or you can zoom in on a single database, to see the relation between the size of a data file and its usage. An example is shown below, where the red, block-shaped graph represents the growth of a database’s physical data file (.mdf) over the past year. The blue, spiked graph shows the increase in data inside the physical file over the same period.
No comments:
Post a Comment