Our tempdb data file balloons some nights to many times its size and a teammate insists switching our main database from full to simple recovery would calm it down.
Is tempdb size related to recovery models at all, and if not, what actually grows it?
The teammate's theory fails at the first fact: tempdb always runs in simple recovery itself, unchangeably and no user database's recovery model reaches into it. Your main database's model governs your main database's log, a different file in a different database, so the switch would calm nothing while costing you point in time restore. What actually grows tempdb sorts into three appetites:
Query workspace, the usual nightly suspect: sorts and hash joins too large for their memory grants spill to tempdb, and index rebuilds with SORT_IN_TEMPDB do their sorting there by invitation. A nightly balloon correlates almost always with the nightly jobs, the maintenance rebuilds or a reporting query whose spill is visible in its execution plan warnings. sys.dm_db_task_space_usage during the balloon names the sessions responsible, worth capturing tonight with a scheduled snapshot since the evidence evaporates by morning.
The version store, the quiet occupant: snapshot isolation and read committed snapshot keep row versions in tempdb for as long as their transactions demand, one long running transaction holding the store's cleanup hostage for hours, growth without any heavy query in sight. sys.dm_tran_version_store_space_usage measures it, and the long transaction it implicates shows in the active transactions thread's queries linked alongside.
Object churn completes the trio: temp tables and table variables from application code and procedures, ordinarily modest, occasionally explosive when a procedure builds giant intermediates per execution across many sessions. The same task space DMV attributes this too.
Managing it rightly: presize tempdb to the balloon size it demonstrably needs, the nightly growth is tempdb telling you its true working size and refusing the message just repeats the file growth stalls nightly, split it into multiple equal data files per current core count guidance for allocation contention, put it on fast storage and stop shrinking it in the morning. The shrink ritual undoes nothing but the file size while guaranteeing tomorrow's growth stalls, the one tempdb habit worth actively unlearning.
Captured the DMV snapshot overnight: the maintenance job's index rebuilds with SORT_IN_TEMPDB owned the balloon almost entirely. Presized tempdb to match across four files and the growth stalls vanished from the job's duration. The teammate has conceded with dignity and we both learned the version store existed.