Attempt to grow all files in one filegroup and not just the one next in the autogrowth chain using trace flag 1117

As you may have noticed, it has been written about Trace Flag 1117 in various blogs, and the general guidance they all provide is to enable it, particularly for SAP and Dynamics.

However one might ask itself the question: In which circumstances, if any, would it be better not to enable it?

The answer to that question is that as long as all security conditions are met so that instant file initialization can be used (i.e. SE_MANAGE_VOLUME_NAME privilege can be set, and TDE is not enabled in that database), there wouldn’t be any performance impact and the general recommendation could be in favor of having this enabled.

It might be bit of a problem though, if storage engine needs to zero the newly allocated space for all the files of the same filegroup, since it grows each file in a serial way. And that only happens when those security conditions explained above are not met.

By the way, something that is no mentioned in any source where that flag is discussed is the very special case under which it would only grow one file, the primary file from the filegroup. And this very edged case would only be seen if what is causing the autogrow is an allocation requested to the sysfiles1 very special system table whose location is well known, and all its pages are required to be allocated from the Primary file. Only in this case, you would see that just the Primary file grows while the size of any other files marked for autogrow in the same filegroup would remain unaltered.

Comments

• Anonymous
  June 16, 2014
  wow, very neat and clear as always, thanks for clarifying this!!

• Matt