Two questions regarding the MDF file size.
1. Recently I encountered numerous autogrow failures. Autogrow was set to 100MB, however the disk space had well over 100GB free space. The question is: What can cause an autogrow failure even when there is enough free disk space?
Note: Decreasing the size of autogrow to 30MB helped.
2. I understand that autogrow is expensive and many recommend resizing the DB up front to prevent autogrow kicking in frequently. I have no problem with this approach. Before I do, I would like to understand: Does the resize work differently than a single autogrow? Specifically, is a large resize (10GB) likely to succeed even when smaller autogrow (100MB) events fail?
I'm aware that autogrow has a time limit (which perhaps resize does not). However, when I refer to autogrow failing, I'm not referring to autogrow just timing out. I'm referring to errors like this:
Could not allocate space for object XXX in database YYY because the 'PRIMARY' filegroup is full.