0

This question already has an answer here:

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?

marked as duplicate by Michael Green, Colin 't Hart, mustaccio, Mr.Brownstone, LowlyDBA Jan 8 at 21:37

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

  • 2
    Examine that sp's execution plan and see if it spiils data to tempdb (due to hash or sort, maybe statistiscs are completely out of date and cardinality estimation is totally wrong) – sepupic Oct 8 '18 at 9:56
  • 1
  • I know it can sound crazy, but why you Truncates the #temp tables instead of dropping them? Is your application keeping every session alive? – Evandro Muchinski Oct 8 '18 at 11:45
  • 4
    You put 16 million rows into a #temp table and then into another #temp table? Yes, this would cause a lot of log activity. See this answer and especially note: log records related to #temp tables can not be cleared out until any containing user transaction finishes. Also if you are going to continue using two #temp tables for this huge amount of volume, just size the tempdb log large and don't let it get small again (it should never be "from nothing" now that you understand activity). Shrinking it only to have it grow every time is very wasteful. – Aaron Bertrand Oct 8 '18 at 12:05
0

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.