SQL Server trace flags

Normally I’m not a big fan of using trace flags, my advice is only to use these when it is absolutely necessary otherwise don’t.

Here is a list of the documented trace flags that you can use with SQL Server. That is the documented once, besides that there is a list of undocumented trace flags – and it is as you might already have guessed one of these I’ll be blogging about today.

During a recent customer engagement, I worked with a busy OLTP system. Their database is running with multiple files and filegroups. Until now they have been manually growing their datafiles, primarily to avoid the autogrowth and ending up with the IOPS being unevenly spread across the drives.

SQL Server fills the datafiles in a round robin fashion based on percent free space, when autogrow occurs the current datafile grows. There is no guarantee that the smallest datafile will be the next to grow. This behavior corrupts your proportional fill pretty quickly.

If SQL Server is running with Trace flag 1117, the behavior is different. When SQL Server determines that it needs to autogrow one of it’s datafiles, it autogrows all the datafiles at the same time. Read further and have a look at the examples, that demonstrates the behavior.

Starting with SQL Server 2016, this behavior is instead configured using the AUTOGROW_ALL_FILES filegroup option, making Trace Flag 1117 unnecessary for most modern environments. The examples in this article therefore illustrate the historical behavior of Trace Flag 1117 and the principle behind synchronized file growth

First of all, I’ll create a database for my tests.

<span style="color:blue;">CREATE DATABASE </span><span style="color:teal;">T1117_NotEnabled
</span><span style="color:blue;">ON
</span><span style="color:gray;">(
    </span><span style="color:teal;">NAME </span><span style="color:gray;">= </span><span style="color:teal;">T1117_01</span><span style="color:gray;">,
    </span><span style="color:blue;">FILENAME </span><span style="color:gray;">= </span><span style="color:red;">'C:T1117T1117_01.mdf'</span><span style="color:gray;">,
    </span><span style="color:teal;">SIZE </span><span style="color:gray;">= </span>3<span style="color:teal;">MB</span><span style="color:gray;">,
    </span><span style="color:teal;">FILEGROWTH </span><span style="color:gray;">= </span>5<span style="color:teal;">MB
</span><span style="color:gray;">),
(
    </span><span style="color:teal;">NAME </span><span style="color:gray;">= </span><span style="color:teal;">T1117_02</span><span style="color:gray;">,
    </span><span style="color:blue;">FILENAME </span><span style="color:gray;">= </span><span style="color:red;">'C:T1117T1117_02.ndf'</span><span style="color:gray;">,
    </span><span style="color:teal;">SIZE </span><span style="color:gray;">= </span>3<span style="color:teal;">MB</span><span style="color:gray;">,
    </span><span style="color:teal;">FILEGROWTH </span><span style="color:gray;">= </span>5<span style="color:teal;">MB
</span><span style="color:gray;">),
(
    </span><span style="color:teal;">NAME </span><span style="color:gray;">= </span><span style="color:teal;">T1117_03</span><span style="color:gray;">,
    </span><span style="color:blue;">FILENAME </span><span style="color:gray;">= </span><span style="color:red;">'C:T1117T1117_03.ndf'</span><span style="color:gray;">,
    </span><span style="color:teal;">SIZE </span><span style="color:gray;">= </span>3<span style="color:teal;">MB</span><span style="color:gray;">,
    </span><span style="color:teal;">FILEGROWTH </span><span style="color:gray;">= </span>5<span style="color:teal;">MB
</span><span style="color:gray;">),
(
    </span><span style="color:teal;">NAME </span><span style="color:gray;">= </span><span style="color:teal;">T1117_04</span><span style="color:gray;">,
    </span><span style="color:blue;">FILENAME </span><span style="color:gray;">= </span><span style="color:red;">'C:T1117T1117_04.ndf'</span><span style="color:gray;">,
    </span><span style="color:teal;">SIZE </span><span style="color:gray;">= </span>3<span style="color:teal;">MB</span><span style="color:gray;">,
    </span><span style="color:teal;">FILEGROWTH </span><span style="color:gray;">= </span>5<span style="color:teal;">MB
</span><span style="color:gray;">)
</span><span style="color:magenta;">LOG </span><span style="color:blue;">ON
</span><span style="color:gray;">( </span><span style="color:teal;">NAME </span><span style="color:gray;">= </span><span style="color:teal;">T1117_log</span><span style="color:gray;">,
    </span><span style="color:blue;">FILENAME </span><span style="color:gray;">= </span><span style="color:red;">'C:T1117T1117_log.ldf'</span><span style="color:gray;">,
    </span><span style="color:teal;">SIZE </span><span style="color:gray;">= </span>1<span style="color:teal;">MB</span><span style="color:gray;">,
    </span><span style="color:teal;">FILEGROWTH </span><span style="color:gray;">= </span>5<span style="color:teal;">MB
</span><span style="color:gray;">)
</span><span style="color:blue;">GO
</span>

Then I’ll create a table and load some data into it

<span style="color:blue;">USE </span><span style="color:teal;">T1117_NotEnabled
</span><span style="color:blue;">GO  

CREATE TABLE </span><span style="color:teal;">T1117_TestData
</span><span style="color:gray;">(
    </span><span style="color:teal;">Id </span><span style="color:blue;">uniqueidentifier default </span><span style="color:magenta;">newid</span><span style="color:gray;">(),
    </span><span style="color:teal;">Created </span><span style="color:blue;">Datetime2 default </span><span style="color:magenta;">sysdatetime</span><span style="color:gray;">(),
    </span><span style="color:teal;">DataValues </span><span style="color:blue;">char</span><span style="color:gray;">(</span>6000<span style="color:gray;">)
)
</span><span style="color:blue;">GO  

INSERT INTO </span><span style="color:teal;">T1117_TestData </span><span style="color:gray;">(</span><span style="color:teal;">DataValues</span><span style="color:gray;">)
</span><span style="color:blue;">VALUES </span><span style="color:gray;">(</span><span style="color:red;">'TESTING 123...'</span><span style="color:gray;">)
</span><span style="color:blue;">GO </span>10000

After running this code, let’s have a look at how SQL Server has expanded the files, this little script helps me with that.

<span style="color:blue;">USE </span><span style="color:teal;">T1117_NotEnabled
</span><span style="color:blue;">GO

SELECT
 </span><span style="color:magenta;">file_id</span><span style="color:gray;">,
 </span><span style="color:teal;">physical_name</span><span style="color:gray;">,
 </span><span style="color:teal;">[file_size_mb] </span><span style="color:gray;">= </span><span style="color:magenta;">CONVERT</span><span style="color:gray;">(</span><span style="color:blue;">DECIMAL</span><span style="color:gray;">(</span>12<span style="color:gray;">,</span>2<span style="color:gray;">),</span><span style="color:magenta;">ROUND</span><span style="color:gray;">(</span><span style="color:teal;">size</span><span style="color:gray;">/</span>128.000<span style="color:gray;">,</span>2<span style="color:gray;">)),
 </span><span style="color:teal;">[space_used_mb] </span><span style="color:gray;">= </span><span style="color:magenta;">CONVERT</span><span style="color:gray;">(</span><span style="color:blue;">DECIMAL</span><span style="color:gray;">(</span>12<span style="color:gray;">,</span>2<span style="color:gray;">),</span><span style="color:magenta;">ROUND</span><span style="color:gray;">(</span><span style="color:magenta;">FILEPROPERTY</span><span style="color:gray;">(</span><span style="color:teal;">name</span><span style="color:gray;">,</span><span style="color:red;">'SPACEUSED'</span><span style="color:gray;">)/</span>128.000<span style="color:gray;">,</span>2<span style="color:gray;">))
</span><span style="color:blue;">FROM </span><span style="color:green;">sys</span><span style="color:gray;">.</span><span style="color:green;">database_files
</span><span style="color:blue;">WHERE </span><span style="color:gray;">(</span><span style="color:teal;">data_space_id </span><span style="color:gray;">= </span>1<span style="color:gray;">)
</span>

And here is the result, when running SQL Server without trace flag 1117

Note: This article demonstrates Trace Flag 1117 for educational purposes. If you're running SQL Server 2016 or later, the preferred approach is to use the AUTOGROW_ALL_FILES filegroup option, which provides similar functionality without relying on a startup trace flag.

Enable Trace flag 1117
There are a few ways to enable a trace flag, that is not the topic of this blog post, so I’ll just show how I have done it for testing purposes. I open a command prompt and stop the SQL Server service, when that is stopped I start SQL Server in console mode and with the T1117 trace flag.

When the SQL Server service is back online, I rerun my tests. I will not show the code again, only the picture that shows the result.

The test clearly illustrates how synchronized file growth improves proportional fill. While modern SQL Server versions provide this functionality through AUTOGROW_ALL_FILES instead of Trace Flag 1117, the underlying principle remains exactly the same.

Using SQL Server 2016 or later?
If you're running SQL Server 2016 or newer, you generally don't need Trace Flag 1117.

Instead, configure the filegroup using AUTOGROW_ALL_FILES, which provides the same behavior in a supported and more flexible way.

This article is therefore primarily relevant for understanding the underlying mechanism or for maintaining older SQL Server installations.

Any questions?
Please reach out to info@inspari.dk or +45 70 24 56 55 if you have any questions. We are looking forward to hearing from you.