Addressing TEMPDB FULL in SQL Server 2019 Availability Group instance

Question

I'd appreciate some advice please? We had a situation where TEMPDB log was full and we were unable to connect to the Primary replica of our SQL 2019 AG from SSMS. We were however able to connect to the secondary replica, where we initiated a failover from the primary to the secondary. The failover rolled back in-flight transactions on the primary tempdb, and so whatever that transaction was that caused the log to be filled up, was rolled back and the log was freed. We were then able to connect to that replica and did a failback to it as the primary.

My question is: Would a restart of the SQL services on each server node be of benefit in avoiding the templog filling up again - i.e. to have clean empty tempdbs recreated?

Answer 1

Would a restart of the SQL services on each server node be of benefit in avoiding the templog filling up again - i.e. to have clean empty tempdbs recreated?

No, the way to avoid TempDB from filling up in an AG (in no particular order, non-exhaustive):

• Making sure TempDB is sized appropriately for the workload and can grow if needed
• Tuning queries that make heavy use of TempDB
• Keep transaction times to a minimum which will reduce the need for Version Store
• Use ADR on the user databases to put the version records in the database instead of TempDB
• Have enough free space on the disks (i.e. if the disk is thinly provisioned)
• Watch for the insidious jdbc implicit transaction behaviors
• Use smaller, fixed growth increments instead of percent auto growth

It sounds like there was a transaction that was open which was holding on to the version store and it eventually grew until TempDB was full given the rest of the uses for that database. It's just a guess, though, based on the symptoms without any data.