0

Having about 8 tempdb data files is a good practice because of large number of object creation and deletions that happen in the tempdb. So sql server can use the data files in parallel.

Given this scenario of more than 1 tempdb data file, sql server uses proportional fill algorithm, thereby filling each data file proportionally. However, when the files are full, then the 1st file is expanded and written to. Then 2nd file is expanded and written to and so on.

So my point is that - the benefit of making 8 data files is lost once the files are full and it behaves as though it is one file only.

Is this question correct? And is there a solution to this - for example so that all files in the filegroup are expanded together. So that proportional fill algorithm can continue using all data files. Is this a problem at all that the dba has to handle or does sql server internally handle it?

1 Answer 1

3

That's correct in case only one file grows in the time:

the benefit of making 8 data files is lost once the files are full and it behaves as though it is one file only

To avoid such cases until SQL Server 2016 (13.x) you could use trace flag 1117:

When a file in the filegroup meets the autogrow threshold, all files in the filegroup grow. This trace flag affects all databases and is recommended only if every database is safe to be grow all files in a filegroup by the same amount.

Trace Flags

But the trace flag affects all databases on the instance. Starting from SQL Server 2016 and later you have an opportunity to configure that behavior at the file group level. For tempdb it's default.

ALTER DATABASE [MyDBName] MODIFY FILEGROUP [MyFileGroup] AUTOGROW_ALL_FILES;
1
  • Isn't it the default that all files grow together in sql 2019?
    – variable
    Commented May 6, 2022 at 8:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.