Trace Flag 1117 - A Safety Net You Might Not Be Aware Of

Are you running SQL 2008 for your SAP instance? If so, are you aware of trace flag 1117?

There are a couple of best practices that are important to keep your SAP instance running well on SQL Server. The first is to ensure that proportional fill is working correctly. A key best practice of proportional fill is to ensure that your freespace is the same across all data files. For this reason, we have always recommended another best practice was to manually grow your datafiles. This was because when SQL Server autogrows data files, it does so one data file at a time in a round robin fashion. When this happens, SQL Server autogrows the first file, writes to it until it is filled and then autogrows the next data file. Now your proportional fill has been corrupted.

In SQL 2008, trace flag 1117 has been created to address this problem. If trace flag 1117 is set, then when SQL Server determines that it needs to autogrow a data file, it autogrows all of the files at the same time. If you want more detail about this trace flag, please refer to SAP Service Marketplace Note 1238993.

We still recommend that you monitor your data files and grow them manually, growing all of you files at one time and by the same amount, but by setting trace flag 1117, you will put a safety net in place that will protect you increase SQL Server beats you to this task.