Sql-server – Increase TempDB Files, Shrink VLF’s with FCI

clusteringfailoversql serversql-server-2012tempdb

I am planning to resize and increase the number of tempDB files and shrink the VLF's on an FCI. In this case, the tempdb files are stored on the network drive, rather than the local disk. I couldn't find anything that addresses situation, but I need to know the impact the increase would have on a FCI. Thanks!

Best Answer

There should be no negative impact on performance esp when you have encountered PFS page contention. Just make sure you dont blindly over create files.

This script from Tigertoolbox (Microsoft Tiger Team) will help.

Depending on the version of sql server you are using, you might need or might not need below trace flag :

  • Trace Flag 1118 – Full Extents Only (tells SQL Server to NOT use “mixed extents” and use “full extents”)

I have not listed Trace Flag 1117 (Grow all files in a filegroup equally) as it affects instance wide. Its better to pre-allocate tempdb files and make sure that they are ALL sized equal.

Finally, make sure that you have set autogrowth to a sensible value.

Few good links to refer :