Normally I’m not a big fan of using traceflags, my advice is only to use these when it is absolutely necessary otherwise don’t. Here is a list of the documented traceflags that you can use with SQL Server. That is the documented once, besides that there is a list of undocumented traceflags – and it is as you might already have guesses one of these I’ll be blogging about today.

Last week I was visiting a customer, their database is running a very 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 IO’s 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 the grow. This behavior corrupts your proportional fill pretty quickly.

If SQL Server is running with Traceflag 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.

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

CREATE DATABASE T1117_NotEnabled
ON
(
    NAME = T1117_01,
    FILENAME = 'C:\T1117\T1117_01.mdf',
    SIZE = 3MB,
    FILEGROWTH = 5MB
),
(
    NAME = T1117_02,
    FILENAME = 'C:\T1117\T1117_02.ndf',
    SIZE = 3MB,
    FILEGROWTH = 5MB
),
(
    NAME = T1117_03,
    FILENAME = 'C:\T1117\T1117_03.ndf',
    SIZE = 3MB,
    FILEGROWTH = 5MB
),
(
    NAME = T1117_04,
    FILENAME = 'C:\T1117\T1117_04.ndf',
    SIZE = 3MB,
    FILEGROWTH = 5MB
)
LOG ON
( NAME = T1117_log,
    FILENAME = 'C:\T1117\T1117_log.ldf',
    SIZE = 1MB,
    FILEGROWTH = 5MB
)
GO

then I create a table and fill some data into it

USE T1117_NotEnabled
GO
CREATE TABLE T1117_TestData
(
    Id uniqueidentifier default newid(),
    Created Datetime2 default sysdatetime(),
    DataValues char(6000)
)
GO
INSERT INTO T1117_TestData (DataValues)
VALUES ('TESTING 123...')
GO 10000

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

USE T1117_NotEnabled
SELECT
 file_id,
 physical_name,
 [file_size_mb] = CONVERT(DECIMAL(12,2),ROUND(size/128.000,2)),
 [space_used_mb] = CONVERT(DECIMAL(12,2),ROUND(FILEPROPERTY(name,'SPACEUSED')/128.000,2))
FROM sys.database_files
WHERE (data_space_id = 1)

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

Enable Traceflag 1117

There are a few ways to enable a traceflag, 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 traceflag.

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. As you can see the result is clear, and just what we expected, the proportional fill works just fine.

@geniiiuscom