TempDB Log File Growth Using Global Temp Tables

Question

This is really a two prong question.

One, I'm experiencing a phenomenon where SQL server consumes a lot of tempDB log file space when using a global temp table while using a local temp table will consume data file space?

Is this normal? I can't find anywhere on the web where it talks about consuming log file space in such a way when using global temp tables vs. local temp tables.

Two, if this is expected behavior, is there any way to tell it not to do this :). I have plenty of data space (6 GB), but my log space is restricted (750 MB with limited growth). As usual, the tempDB is setup with Simple Recovery so running into the log file space limit has never been a problem before ... but I've never used global temp tables like I'm using them before either.

Thanks!! Joel

Answer 1

What is the lifespan of one of these global temp tables? Are they dropped in a reasonable time? "Regular" temp tables get dropped when the user disconnects, if not manually before then, and "Global" (##) temp tables get dropped, if memory serves, when the creating session ends. I can see the log growing if the global temp tables last for a long time, because it could be that the log records governing the temp table activity are still marked as active log records and don't get freed with log backup (full recovery) or checkpoints (simple)

Answer 2

The length of the session will have an impact as noted above.

Also, temporary tables work within transactions and table variable work outside the context of transactions. Because of this the temporary will log entries in the log file that relate to the updates to the use of the table.

Answer 3

When either form of temporary table is created (local or global) the table is physically created and stored in the tempdb database. Any transactional activity on these tables is therefore logged in the tempdb transaction log file.

There is no setting per say however, you could implement a physical table as opposed to a temporary table in order to store your data within a user database, thereby using the associated data and transaction log file for this database.

If you really want to get stuck in and learn about the tempdb database take a look at the following resources.

Everyning you ever wanted to know about the tempdb database