Its widely know that a best practise for space management for more important SQL data and log files is to ensure that growth events are handles manually so that there is no outage while the growth events happens. Of course most people still leave auto grow on in case the growth event happens while the DBA is asleep, on holiday or sacked.
Paul Randal has a good survey with notes on database autogrow here
http://www.sqlskills.com/BLOGS/PAUL/post/Importance-of-data-file-size-management.aspx
As Paul points out with SCOM versions before 6.1.314.36 if Auto grow was enabled the management pack did not alert when log or data files filled up, which was always a bit of a joke as it seems that the SCOM SQL MP designers did not realise that its not the running out of disk space we want alerting on its the growth event itself which is the performance risk. The running out of disk space is not a performance risk (its a more serious availability risk)
Anyways, Paul did raise this at an MCM class and lo and behold good news, in the new version SQL MP for SCOM now alerts if auto grow is enabled. It looks like they really went to town on the little feature by independently tracking space for files and file groups too.
Bad news is reading the blurb, it looks like the SQL SCOM MP has still missed the whole point and has decided to only alert if the data or log file is going to fill up the ENTIRE disk, not the file. Ohh dear, what a shame as it looks like a lot of effort went into the coding of this feature.
Here is the blurb from the new SQL Management Pack:
No Auto Grow
For a file that has no auto grow, available space would be the difference between the initial size for a file and the used space.
Auto Grow Enabled
With auto grow enabled, the available disk space is included as part of the over available space. In addition to the difference between the file size and the used space, the available space for files with auto grow enabled would be the minimum of either the difference between the max size and the file size and the free space left on disk.
Low available space for database files that have auto grow could mean that the file is approaching the limits of the hosting logical drive. For files with auto grow enabled with a max size, low available space could also mean that the file is approaching the max size specified for a file.
The available space calculations also take into account that the file can also fail to grow if the growth amount of the file is greater than the amount left on disk and that the file cannot grow if the difference between the max size and the current file size is less than the growth amount. In these situations, available free space left on disk is not included as part of the available space because we cannot grow any longer.
—
More bad news. The implementation of the feature adds some FOURTEEN rules and rollups to EACH database. On our one test server the health explorer view for a SQL Server is many times slower to load. Think about it, fifty user databases with 14 monitors and rollups per database = 700 rules and rollups to query and load on the GUI per server on the health explore, just for one little rule. If only fifty rules were implemented in this fashion we would have 35,000 rules and rollups on the health explorer screen for our one server. This doesn’t sound too scalable at all to me.
Example SCOM health explorer (above) with new space tracking feature and all the rules and rollups it creates. Pity that it:
a) Still doesn’t alert on growth events.
b) Causes the Health Explorer to load much slower
