Thursday, May 25, 2017

About Trace Flag -T 1117

In your SQL Server environment for any data base having more than one data file along with temp database data files with the same size and with auto-grow option.

Lets assume that you have a database with eight files & initial size of 1024 MB and auto-grow of 200 MB each.

The default behaviour is for one of these eight files to grow by the specified increment. This will happen when all of the files are in full and after one file to grow by the specified increment. This will happen when all the files are full, and after one file grows and SQL Server algorithm will direct new extent allocation to the file with the most available space, so new extents will be written to the one file that just grew.

To overcome this we have the trace flag 1117, it changes the behaviour of file growth. If one data file in file group grows, it forces other files in that file group to also grow.

All the files have about same percent of free space after auto-grow and the benefits of evenly distributing IO will be preserved

But Trace 1117 applied to the entire SQL Server instance. you can't apply to any one specific database. It will effect all files in the same filegroup of database. If the database having multiple filegroups and auto-grow event occurs in one filegroup will not affect the other filegroups.