I've a SQL 2005 installation and my templog.ldf file keeps growing to consume all the free space on the drive it's on. Sometimes it'll stop with a few mb free but sometimes it goes further, this being the c drive I think this behaviour may be implicated in some other issues I've been seeing.
My question is, what should I do, I can move the log to another drive but I've reason to assume it won't just do the same thing there. I'm assuming that this behaviour is likely as a result of something I can change and that 45gb is an unusual size for the tempdb log to get to. We do use a lot of temporary tables and table valued functions in our code so there is plenty of scope to use tempdb, I can understand the tempdb database growing but don't understand the reason for the growth of the templog.
So far, I've ran
DBCC OPENTRAN('tempdb')
to see if any old transactions are hanging around, they aren't. I've read about how to shrink the tempdb and have done this a few times, but I'm really wondering what if anything I can do to stop this happening in the first place or more details on why it might be growing so much in the first place.
==EDITS==
1) The tempdb is using simple recovery model
2) The growth in templog occurs over a couple of hours in the morning when we have some scheduled queries running, basically a load of reporting which runs out of office hours for the day ahead. The size of the file steadily grows over this time. We control how many concurrent reports are running at the same time, increasing the number of concurrent reports increases the rate at which the log grows.
Best Answer
Check your reporting queries. Do you have any that have DISTINCT in them? Do any of them have a cartesian joins?
Do any of the reporting queries access linked servers as members of a join? If so this can cause tempdb log and database to grow.
When the reports are running in the morning do any of them crash?