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

4 Related questions 0 SQLServer tempDB growing infinitely 2 Sql Server 2000 - tempdb growing very large 2 TempDB Log File Growth Using Global Temp Tables Related questions 0 SQLServer tempDB growing infinitely 2 Sql Server 2000 - tempdb growing very large 2 TempDB Log File Growth Using Global Temp Tables 7 SQL Server Tempdb LOG file growing 0 sql log file growing too big 4 Sql Server ldf files growing very fast 0 SQL Server log file is too big 3 "tempdb.mdf" increasing to astronomical sizes (i.e. 800GB) due to SQL query 15 Sql Server LDF file taking too large space 0 I have a problem with my database log file size Load 7 more related questions Show fewer related questions Sorted by: Reset to default

Know someone who can answer? Share a link to this question via email, Twitter, or Facebook.

Your Answer

Sign up or log in

Sign up using Google Sign up using Facebook Sign up using Email and Password Submit

Post as a guest

Post Your Answer Discard

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.