Should You Enable Autogrowth on SCOM Operations Database?
You should put a fair amount of thought into designing your SCOM infrastructure. You need to make sure not only that the design works efficiently for the existing requirements you have, but also that you will be able to accommodate the future growth and expand your current Management Group without compromising its efficiency or performance.
There is an excellent Microsoft sizing tool that suggests the exact infrastructure and size you may want to go with while designing your Management Group.
Now, about the Operations DB in particular — Operations DB is designed to store only the most recent data, and that is why it is not supposed to be as big as DW DB. Also, the general recommendation from Microsoft is that there needs to be at least 40% free space at all times and an advised 50% when doing a major version upgrade. SCOM throws an alert when it drops down 40%. You have to keep this in mind at the time of designing the Operations DB size.
SQL gives you the option to autogrow your Operations database. You can enable this to allow the DB to grow in size along with your overall SCOM environment. In my opinion, you should not do so, however, unless you are absolutely forced to.
WHY NOT ALLOW THE OPERATIONS DB TO AUTO-GROW?
https://www.infrontconsulting.com/uploads/images/blog/SCOM-database-autogrowth.png
If the Operations DB keeps on auto-growing, you will not know. Why do you need to know about growth? As I mentioned earlier, the database is not supposed to grow drastically. If more and more data piles up in the Operations DB without timely grooming of the old data, you have a problem. An overloaded database might seriously affect the performance of your Operations DB and in turn your whole SCOM infrastructure.
When the database auto-grows, it suspends the transactions temporarily. Meaning activities happening in your SCOM-monitored environment during this period are kept on hold for insertion into the Operations DB. This might result in delayed alerting and/or data loss. The growth increment might be so large that it gives a time-out on the job SCOM was running.
If you set up auto-grow with a smaller value in percentage or megabytes, chances are the database will stop and grow very frequently and might cause disk fragmentation. Again, you risk losing your data or timing out the currently running job (which is causing the auto-grow).
WHAT DO YOU DO IF THE OPERATIONS DB STARTS ALERTING ON LOW DISK SPACE?
First you see if you can groom some old data out. Keep the retention period less than seven days if you don't need to hold onto data for longer for a specific purpose. In fact, this is one of the first things I do whenever I take over a larger environment. You may need to wait a couple days for new settings to take effect as SCOM goes through its retention cycle. If you need to groom it immediately, you can force the grooming as well.
Even if the grooming doesn't work, and you must increase the size of your Operations DB, do it manually. Keep in mind the requirement to keep 40% of the DB free space and try to forecast your future workload.
Usually we set the initial size of the Operations DB after the SCOM installation is finished, by using SQL management studio and setting the file sizes and sometimes SQL DB file striping manually. The DW database we set to a value large enough to start with, then setting the auto-grow increments to a number of MB which is not too small and not too large (and not using a percentage increase).
The autogrowth setting is enabled on DW and ACS DB but disabled by default on Operations DB by Microsoft and there's a reason why.
From Microsoft official documentation:
"Only rely on autogrow as a contingency for unexpected growth. Autogrow introduces a performance penalty that should be considered when dealing with a highly transactional database."
So, in short, remember:
Initial Size of Operations DB = Final Size of Operations DB
At least for the most part. If you must increase it, allocate more space to it manually.