We turned on the row level versioning on our database. It's on for almost 15 days. But this morning the tempdb transaction log got full.
My question is if the overhead on the tempdb database is on its transaction log only not in the whole database.
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.
Thanks in advance.
For this new functionality, there will be a cost as well. Let us look at the cost to conclude.
1 Increases resource usage when modifying data since row versions are maintained in tempDB.
2 Update and Delete transaction will use more resource since it has to create a snapshot in the tempDB. This could cause higher IO, CPU and Memory usage.
3 TempDB must have enough space to handle all the additional requirements.
4 14 Bytes will be added to the row in the database to keep track of the versions.
5 If there are long version chains then Data Read performance will be affected.
-------Bhuvnesh----------I work only to learn Sql Server...though my company pays me for getting their stuff done;-)