January 9, 2014 at 12:33 pm
Greetings,
I've got a prod server that recently experienced growth of about 17 GB on the tempdb log file over the course of several days. That file is set to 5 GB in size by default and basically since this server entered production about two years ago there have been no issues with that. No auto growth required and no need to shrink it. late last month and just today there have been occurrences where a developer has left a session running on their machine and the query ends up getting suspended. When this happens the log file in tempdb starts to grow.
My question isn't "why does tempdb grow?" as I understand how order/group by, hash joins, etc. utilize this database. My question is why did two suspended sessions seem to cause the log file for this database to get out of hand? I've searched for an explanation on this but everything mostly discusses the data files and not the log file. These two queries didn't involve joins as they were selecting from a single table. However, these two tables are very popular to applications. I can understand how the queries would be suspended given the potential for locking, etc., but I just can't get my head around why the tempdb log grew so much.
Cheers
January 9, 2014 at 4:38 pm
If either of these suspended sessions had an open transaction in tempdb (i.e. related to a temp table), then the tempdb log would continue to grow until these transactions were either committed or rolled back.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply