Monitor Autogrowth settings of SQL Databases

Question

Hello,

I first asked this question in a Q&A forum for another product, so here is the question again: :-)

I'm looking for a way to monitor the autogrowth settings (Autogrowth enabled, File Growth, Maximum File Size) of my SQL databases.
Is that possible with SCOM and how can I configure it?

So far I haven't been able to find anything on the Internet.

BR

Answer 1

Hi @Julian ,

This should be possible to do with SCOM, you can use SCOM to trigger the scripts that checks the SQL Server setting for autogrowth, file growth and maximum file size.

You will first need to figure out how to query the above, once you have the script working you can create either a monitor or rule (depending on your need) in SCOM that triggers the script.
If you create for example a two-state monitor, you can define which of the setting/threshold is "Healthy" and which is "Unhealthy", for example:

• AutoGrowth is enabled = Unhealthy
• AutoGrowth is disabled = Healthy

If you want to write the script in PowerShell, you can make use of Cookdown's PowerShell authoring management pack which allows monitors, rules and tasks to run PowerShell scripts.
There's a webinar video in the link which you can have a look at to learn how to create a sample monitor that triggers a PowerShell script.

----------

(If the reply was helpful please don't forget to accept as answer, thank you)

Best regards,
Leon

Answer 2

As for getting the info in SQL Server, you have two options. None of them are 100% reliable:

Querying the default trace. The old trace infrastructure is deprecated, but it is still there. But, the events might have ages out from the default trace, since you have 5 files with max 20 MB per file.

Querying the backup history tables and get the file size from there. This requires that you indeed do backup all databases, and that the backup history tables haven't been purged.

Other options that require you to actively do something to capture the information include:

Create your own trace to capture grow operations. I was going to say Extended Event trace, but there doesn't seem to be an event foe data file grow, only log file grow. So possibly that you have to be the old trace engine. Anyhow, you now control where the trace info goes and how many rollover files you want and size for them. And, of course finally query those files instead of the default trace.

Create your own table in which you keep track of database file size and have a scheduled job that inserts data into this table (possibly only when there has been a change since last time you looked). And then query that table.

It is up to you which of above options suits you best as information source. What then is left for you is to get SCOM to trigger based on the info from whatever of above you choose. This isn't an SCOM forum, so when you decided on information source, you can continue this in an SCOM forum. Perhaps using a WMI alert?

Answer 3

Thx for your tips, i will ask the question in scom forum. :-)