So using the Perf and Diagnostics tool no matter how many times I run it Epicor always ends up Failing the SQL Config check because of “SPACE”. The Fail Message (Warning in this case) looks like this
My server is setup to Auto Grow at 10% without Limit, it currently (according to the Perf and Diag check) sits with 15+GB of available space on the DB. Why is Epicor hellbent on me pre-allocating extra space to my MDF file?
Most if not all of us are on SSD’s or Fusion Drives now, and I have 600GB of space available on my drive (set to auto grow)
Why does the config check, flag this as an issue and more importantly why does it Fail the config check? I would live with a Warning or just an Info note. Sure I could ignore it, but I always want my config check to be “GREEN” across the board! What gives? @Bart_Elia do you have any insights into this?
From my research online not pre-allocating space could cause a performance hit (maybe) but that paper I read was for spinning disks and it was written in 2008.
aidacra
(Nathan your friendly neighborhood Support Engineer)
2
Autogrowth should only ever be considered as a pressure relief valve to an acutely bad situation (no more free space in the database - that’s really bad!) and should never be considered the strategy for database right sizing. Every time autogrowth events occur, there is a cost: time @@ (I’ll come back to the @@ in a moment) and depending on the disk subsystem, fragmentation of the physical files themselves.
Let say you have a large-ish database at 200GB. No more free space in the database file. End user adds a record to the system, and the autogrowth event happens. By default the autogrowth settings expand the database files by 10% - well, adding 20GB to the mdf takes more than zero time and in some situations I’ve seen it take more than the autogrowth timeout that SQL has (30 or 60 seconds; I don’t recall the specific timeout) and it rolls back the operation.
You may be wondering if our concern is with a large autogrowth event at one time that could cause a transaction to roll back/timeout in the middle of the day(and cause issues that present in many different ways), why doesn’t the Config Check just check one of the following instead?
the autogrowth settings themselves to make sure it never grows more than 1GB at a time instead of the default 10%?
OR
@@ that SQL server service execution account has the database instant file initialization privilege so autogrowth is almost a zero millisecond operation?
If you are comfortable with the PROs and CONs of those adjustments then those are definitely options available to you. We ultimately decided that the Config Check should be used to encourage people to right-size their database to prevent any autogrowth events (which is the best case) instead of affecting how long the autogrowth event takes as our view is that autogrowth events should be avoided all together. Regarding the @@, there are some legitimate security concerns when that is enabled, but, SQL 2016 presents users with the option of granting the SQL Service user account the zero initialization privilege so…
TL/DR: with this rule we’re trying to prevent one of many possible issues from occurring at all and to help customers get into the habit of knowing their db growth patterns. For best possible performance and stability databases need a little TLC from time-to-time, and making sure there is enough free space in the database for new transactions (aka: right sizing) on a regular/planed basis is a very good thing.
So is it the recommended Epicor process then to monitor the database growth and then once a month resize the MDF to accommodate one month’s worth of growth?
I do this on a yearly basis. If you have the disk space it is better to just grow it large fewer times. Definitely do not let the server do it.
1 Like
aidacra
(Nathan your friendly neighborhood Support Engineer)
7
After several months you’ll determine what your normal growth pattern looks like. I would personally plan on pre-allocating empty space (aka: right-sizing) annually based on historical norms and set up alerts within SQL (lots of approaches to do that) to alert you when you getting close to X amount of free-space. But, I am very energy frugal when it comes to monitoring things–automate everything that can be automated. SPOILER: almost everything SQL related can be automated
PDT developer here, can you share your config check output as an excel spreadsheet?, in your screenshot its showing a warning, i.e. take action on it soon or you’ll face the consequences, it isn’t an error line.
LoL