TempLog.ldf Grows Too Large

Question

We have a stored procedure that we run regularly. It blows up the size of TempLog.ldf from nothing to 150 GB in the course of 3-4 hours. What's going on and why is it so big? TempDB.mdf is a constant 40GB. That's a fine size, and it never gets bigger. The Recovery model for TempDB is set to Simple. AutoShrink is off.

The procedure does not use any explicit transactions. It works through roughly 500 chunks of data. Some are very small, only a couple hundred rows. Some are very large, 1 - 5 Million rows. One chunk has 16 million rows.

For each chunk of data the procedure:

1. Reads its rows from a persistent table into a #temp table 1.
2. Copies those rows from #temp_table_1 into another #temp table 2, processing them as it goes.
3. Creates a non-clustered index on #temp_table_2.
4. Does a number of updates on #temp_table_2.
5. Deletes rows from #temp table 2 where a specific column contains a null.
6. Copies rows from #temp_table_2 into a "persistent" table.
7. Truncates the #temp tables.

As I said, the size of TempLog.mdf consistently stays at 40GB which is fine. I need to know why TempLog.ldf gets so large and what I can do to mitigate the problem. I tried adding a manual checkpoint for TempDB at after the #temp tables have been truncated. That doesn't appear to make a difference.

Questions:

1. What goes on in TempLog.ldf?
2. Does creating an index on a #temp table take a lot of space in TempLog.ldf?
3. Why isn't any of this space released from TempLog.ldf when the procedure ends?
4. Can I inspect the contents to determine what's taking so much space?
5. Any other ideas about what might be going on?

Answer 1

You can observe transaction log usage in Tempdb with DMVs, and then possibly instrument your procedure and monitor its transaction log usage in tempdb to see if you can account for the log file growth and possibly identify what's contributing to it.

EG:

select database_transaction_log_bytes_used /1024. log_mb_used
from sys.dm_tran_database_transactions dt
join sys.dm_tran_current_transaction ct
  on dt.transaction_id = ct.transaction_id
where database_id = 2