We have a stored procedure that we run regularly. It blows up the size fo 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 records. Some are very large, 1 - 5 Million records. One chunk has 16 million records.

For each chunk of data the procedure

1. Reads its records from a persistent table into a #temp table 1.
2. Copies those records from #temp table 1 into another #temp table 2, processing them as it goes.
3. Creates an index on #temp table 2
4. Does a number of updates on #temp table 2.
5. Deletes records from #temp table 2 where a specific field is null
6. Copies records from the second temp table into a persistent table.
7. Truncates the temp tables

As I said, the size of TempLog.mdf consistently stays at 40GB. 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. Can I inspect the contents to determine what's taking so much space?
4. Any other ideas about what might be going on?

Thank You, Robbie