rafa.aborges (4/20/2010)
My question is if the overhead on the tempdb database is on its transaction log only not in the whole database.
Row versioning does not directly consume additional tempdb log space.
Additionally, when tempdb runs out of space, SQL Server attempts to shrink the version store.
Is your transaction log for tempdb stored on the same physical device as tempdb data?
Are your tempdb files set to auto-grow?
Did tempdb fill the available disk space?
Because we didn't see much change on the database itself, it's almost at the same size that were before we turned on the row versioning, but the transaction log increased absurdly until the transaction log disk got full.
SQL Server provides a number of ways to keep track of the version store in tempdb, including the sys.dm_tran_version_store DMV. You should follow the normal process to determine why the transaction log for tempdb grew so large. Typically, it will be due to a long-running user transaction.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi