Why is my TEMPDB database so big? Reasons of extreme growth.

Sometimes I got questions from people asking why the TEMPDB database is soo big, especially in comparison with the size of the user databases. Triggers can be the reason for this. For instance an update trigger in combination with a lot of updates. Row versioning is a general framework that is used to support triggers. Every change (old and new value) made by the trigger is stored in the tempdb database. Row versions are held in the tempdb version store for as long as an active transaction must access it. This means that long running transactions with a lot of updates will result in a lot of entries in the transaction version store. The content of the current version store is returned in sys.dm_tran_version_store.  You can use the version_store_reserved_page_count column in sys.dm_db_file_space_usage to view the current size of the version store. More information about how tempdb is used can be found here. Use the SQL Profiler to see which updates are executed and analyze if you can update these records in a more efficient way.