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
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
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.
Leave a Reply