A while back I was working on setting up a new Availability Group (AG), which required working with tempdb. It was a bit of an advanced setup in the sense that it was a five node cluster. Four of the nodes set up in a standard AG – two active/passive AGs. The fifth node was a participant in both AGs as a read replica. The logic behind the fifth node was it was being used as a read-only data warehouse environment, with the need of having all the databases in both AGs on one server. The fifth node was never to be made an active primary.

Eight tempdb files are the standard

One of the differences with the fifth node was that it had 12 CPUs vs 8 as with the other four nodes. So with that, the fifth node was now in the terrority of needing more than the standard 8 tempdb files, 12 to be exact. After adding the four extra tempdb files, I noticed that after a few days the files weren’t growing.

TempDb recommendations

One of the recommendations regarding tempdb files, pre-SQL2016, was to enable TF-1118 to grow the tempdb files at the same rate. Otherwise, one or two files would become a hotspot – meaning only one or two of the tempdb files would be getting used.

To resolve the issue, I attempted to resize the other eight files to make room to grow the additional four. While trying to shrink one of the eight Tempdb files I received the following error:

Work table



A little Googling turned up the cause of the error, occasionally TempdDB files are stubborn and will not always shrink. One of the suggestions I found was to try to run DBCC FREEPROCCACHE.However, that didn’t quite work for all of the files. A little more Googling turned up similar conditions at SQLSERVERCENTRAL.COM and one of the suggestions was to bump the size of the file by a few MBs. Which ended up working for me.

Next time I have to shrink a tempdb file, I’ll have a process to start with to unstick stubborn files.


Leave a Reply

%d bloggers like this: