I had a session about trace flags this year on the SQL Day 2014 conference that was held in Wroclaw at the end of April. The session topic is important to most of DBA's and the reason I did it was that I sometimes forget about various trace flags :). So I decided to prepare a presentation but I think it is a good idea to write posts about trace flags, too.

Let's start then - today I will describe the TF 1117. I assume that we all know how to setup a TF using starting parameters or registry or in the session or on the query level. I will always write if a trace flag is local or global to make sure we know how to use it.

Why do we need this trace flag? Let’s create a test database first. This is quite ordinary database as it has two data files (4 MB each) and a log file that has 1MB. The data files are able to expand by 1 MB and the log file grows by 10%:

USE [master]

GO

CREATE DATABASE [TF1117]

 ON  PRIMARY

( NAME = N'TF1117',

     FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014\MSSQL\DATA\TF1117.mdf' ,

     SIZE = 4096KB ,

     MAXSIZE = UNLIMITED,

     FILEGROWTH = 1024KB

),

( NAME = N'TF1117_1',

     FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014\MSSQL\DATA\TF1117_1.ndf' ,

     SIZE = 4096KB ,

     MAXSIZE = UNLIMITED,

     FILEGROWTH = 1024KB

)

 LOG ON

( NAME = N'TF1117_log',

     FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014\MSSQL\DATA\TF1117_log.ldf' ,

     SIZE = 1024KB ,

     MAXSIZE = 2048GB ,

     FILEGROWTH = 10%

)

GO

Without the TF 1117 turned on the data files don’t grow all up at once. When a first file is full the SQL Server expands it but the other file is not expanded until is full. Why is that so important? The SQL Server proportional fill algorithm will direct new extent allocations to the file with the most available space so new extents will be written to the file that was just expanded. When the TF 1117 is enabled it will cause all files to auto grow by their specified increment. That means all files will have the same percent of free space so we still have the benefit of evenly distributed IO. The TF 1117 is global flag so it affects all databases on the instance. Of course if a filegroup contains only one file the TF does not have any effect on it.

Now let’s do a simple test. First let’s create a table in which every row will fit to a single page: The table definition is pretty simple as it has two integer columns and one character column of fixed size 8000 bytes:

create table TF1117Tab

(

     col1 int,

     col2 int,

     col3 char (8000)

)

go

Now I load some data to the table to make sure that one of the data file must grow:

declare @i int

select @i = 1

while (@i < 800)

begin

      insert into TF1117Tab  values (@i, @i+1000, 'hello')

       select @i= @i + 1

end

I can check the actual file size in the sys.database_files DMV:

SELECT name, (size*8)/1024 'Size in MB'

FROM sys.database_files 

GO

 

As you can see only the first data file was  expanded and the other has still the initial size:

 

name                  Size in MB

--------------------- -----------

TF1117                5

TF1117_log            1

TF1117_1              4

There is also other methods of looking at the events of file autogrows. One possibility is to create an Extended Events session and the other is to look into the default trace file:

 

 

DECLARE @path NVARCHAR(260);

SELECT    @path = REVERSE(SUBSTRING(REVERSE([path]),

         CHARINDEX('\', REVERSE([path])), 260)) + N'log.trc'

FROM    sys.traces

WHERE   is_default = 1;

SELECT    DatabaseName,  

              [FileName],  

              SPID,  

              Duration,  

              StartTime,  

              EndTime,  

              FileType =

                        CASE EventClass       

                             WHEN 92 THEN 'Data'      

                             WHEN 93 THEN 'Log'  

          END

FROM sys.fn_trace_gettable(@path, DEFAULT)

WHERE   EventClass IN (92,93)

AND StartTime >'2014-07-12' AND DatabaseName = N'TF1117'

ORDER BY   StartTime DESC;

 

After running the query I can see the file was expanded and how long did the process take which might be useful from the performance perspective.

 

 

Now it’s time to turn on the flag 1117.

DBCC TRACEON(1117)

 

I dropped the database and recreated it once again. Then I ran the queries and observed the results. After loading the records I see that both files were evenly expanded:

name                  Size in MB

--------------------- -----------

TF1117                5

TF1117_log            1

TF1117_1              5

I found also information in the default trace. The query returned three rows. The last one is connected to my first experiment when the TF was turned off.  The two rows shows that first file was expanded by 1MB and right after that operation the second file was expanded, too. This is what is this TF all about J