Day 6–Trace Flag 1117– Auto Grow Equally in all Data file

Today we are going to see the trace flag 1117. Have you ever heard or read that all your data file should be in same size? Wonder why? Let’s assume you have four files in a database which is not equally sized then SQL Server will utilize the data file which is largest in size, assume you have auto growth option enabled then obviously that file will grow and SQL Server will keep on using the data file which is largest in size.

To overcome this you need to setup the data file in equal size. However in some circumstances we wont be able to keep same file size for all data files , so to overcome this we have the trace flag 1117 which will auto grow all the data file equally even if we didn’t set data file size equally. Let’s do a test on this

Run the query below to create a database 1117 with 4 data files and we are going to populate some data (approx 500 rows of size 8KB=4MB), default size is 3 MB so now autogrow should happen

--Create database with 4 files keeping unequal size
USE master
GO
IF EXISTS(SELECT 1 FROM sys.databases where name='1117')
DROP DATABASE [1117]
GO
CREATE DATABASE [1117]
ON  PRIMARY 
( NAME = N'1117', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\1117.mdf' 
, SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ), 
( NAME = N'1117_1', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\1117_1.ndf' 
, SIZE = 1024KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ), 
( NAME = N'1117_2', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\1117_2.ndf' 
, SIZE = 1024KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ), 
( NAME = N'1117_3', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\1117_3.ndf' 
, SIZE = 1024KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
LOG ON 
( NAME = N'1117_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\1117_log.ldf' 
, SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO
--script to get file size initially
SELECT name, (size*8)/1024 'Size in MB' FROM [1117].sys.database_files 
--Now CREATE a table to populate the data 
USE [1117]
GO
CREATE TABLE details (name CHAR(8000))
GO
INSERT details VALUES ('SQL-Articles.com')
GO 500
--script to get file size after data insertion
SELECT name, (size*8)/1024 'Size in MB' FROM [1117].sys.database_files 
GO

trace_flag_1117_1

From the output above you can note that only data file 1 is autogrown and rest of the files are still in initial file size. So to over come this add the trace flag to startup parameter and restart your SQL Server to get effective. Check my article “Enable \ Disable Trace Flag in SQL Server” for enabling the trace flag. Once you enable the trace flag run the above test query and see the file growth

trace_flag_1117_2

From this image you can see after enabling the trace flag data file growth happened equally in all the data file.

To conclude you can utilize this trace to enable equal file growth in all data file however enabling this will not be for a single specific database it’s for all the database attached to that instance. So please be careful while enabling it and do necessary tests in development server before promoting it to production.


Posted

in

by

Comments

5 responses to “Day 6–Trace Flag 1117– Auto Grow Equally in all Data file”

  1. Day 6–Trace Flag 1117– Auto Grow Equally in all Data file – SQL-Articles…

    Thank you for submitting this cool story – Trackback from DotNetShoutout…

  2. […] 0 As you may have noticed, it has been written about Trace Flag 1117 in various blogs, and the general guidance they all provide is to enable it, particularly for SAP […]

  3. prastogi avatar
    prastogi

    Hi Vidhyasagar,
    I think files grow and uses in round robin fashion, for example if we are having 2 data files where both are size of 4 MB each(autogrow by 2 MB). According to round robin. In your case transaction is using only 1117 file so it is grow by 1 MB and transaction completed, but by usng this trace flag it will also grow second file by 1 MB , which is not required for that transaction as well as cause to slowness because auto growth operation for second file will also use some resources.
    Thanks :
    prastogi

    1. VidhyaSagar avatar

      Hi Prastogi,

      If you have more than one file in a filegroup and if the free space is unevenly distributed then data will not be proportionally filled in both the files so you might end up more IO on a particular file. That’s the reason we always ask to keep same size in all files and this is a best practice from Microsoft. If you enable instant file initialization then file growth will done seamlessly. Check out the doc http://download.microsoft.com/download/d/9/4/d948f981-926e-40fa-a026-5bfcf076d9b9/SAP_SQL2005_Best%20Practices.doc fore more details.

  4. […] the-autogrowth-chain-using-trace-flag-1117 Link: http://sql-articles.com/articles/general/day-6trace-flag-1117-auto-grow-equally-in-all-data-file Link: http://www.ryanjadams.com/2017/05/trace-flag-1117-growth-contention/ Âmbito: apenas […]

Leave a Reply to VidhyaSagar Cancel reply

Your email address will not be published. Required fields are marked *