SQL Server TempDB – Transaction Log Issue and Performance

performancesql servertempdb

I've worked with SQL Server for some time and I understand the various issues that one can run into with TempDB. But, with this latest issue, if I have my issue figured out correctly, is it possible that a statement with a BEGIN TRANSACTION that uses tempdb tables/objects that is left hanging (no commit) can hold the tempdb LOG file "open" and not allow tempdb to clear its own log? This leads to the tempdb log file eventually filling up and causing issues in the SQL Server. I find this hard to believe that there isn't some sort of safeguard in place within SQL Server that would somehow allow the tempdb LOG to not be affected If not, I find this disturbing…

Best Answer

TempDB is no different than any other database. All actions must be logged, and log space will only be reused once the log records for a given transaction have either been committed or rolled back.

As @KrisGruttemeyer said in a comment, you can find out what type of action is causing the transaction log to grow by looking at:

select d.name, d.log_reuse_wait_desc 
from sys.databases d 
where d.name = 'tempdb';

This will likely show you either 'active transaction' or 'nothing' for tempdb. If you see active transaction you know some transaction is open and has not been committed yet. You can see what transactions are open by looking at various system DMVs including, sys.dm_tran_active_transactions, among others.

For a much more in-depth look at what is causing your tempdb to grow, check out this question.

Related Question