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.
We had a similar issue, after having raised PSS call with Microsoft and in-depth investigation of the issue we zoned into the following possible cause and resolution.
Cause:
The probable cause for the symptoms are due to disks/lun's on which user databases are placed having severe I/O response issues; this causes the automatic checkpoint on user databases to take very long to finish.
Now, checkpoint on tempdb occurs only when the tempdb log becomes 70% full and also it has a lower priority than user database checkpoints. So, effectively when automatic checkpoint on user database/s is issued and is trying to complete, due to heavy tempdb usage causes the tempdb log file to fill up quickly; at 70% log usage the tempdb checkpoint occurs but is queued behind the user database checkpoint.
In the time it takes for the user database checkpoint to finish the tempdb log file keeps getting filled up and if autogrow is set the log file grows when it requires more space. This is the reason the log file keeps growing.
In summary, the most possible root cause for the symptoms you describe are due to poor I/O response from the disks/lun's for your user and/or tempdb database/log files.
Solution:
We worked around the issue while we sorted out the I/O subsystem by setting up an alert which fired when the tempdb log file became 75% full and in response executed a job which forced a manual "CHECKPOINT"(which takes precedence over automatic system checkpoints), clearing out the tempdb log preventing it from auto-growing indefinitely. It is still a good idea to leave the log file on auto grow for any other eventuality. Also, I strongly recommend you to consider reducing the tempdb log file size to something meaningful as per your environment after you put the fix in.
Hope this helps.
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?
What is the Recovery Model set to on the temp db? If it's not set to Simple, then set it to Simple. This should keep it from growing. If it's already set to Simple then I'd say there's an underlying problem that needs to be addressed and any attempt to shrink the file is merely treating the symptoms of the problem and not the root cause.
I've spent the last few hours reading and making notes on this
http://technet.microsoft.com/en-gb/library/cc966545.aspx
There's a lot of detail in there and suggestions for trouble shooting issues. It seems that unless your tempdb is expanding and never stops growing it's probably just taking up the amount of space it needs and should have been configured to be that size initially. There is a section on estimating the space required for your tempdb as well as tracking down what might be taking up space in tempdb. As a result of this the first thing I'm going to do is move tempdb to a larger drive and see what happens from there.
There is a section titled 'Space required for tempdb logging' which indicates which features use the log, there is another earlier section which details the superset of features which use tempdb.
The section titled 'Monitoring I/O' has a few ideas on performance counters to watch, a quick look at my server put these in you've-probably-got-an-io-bottleneck territory. I'll monitor these for a while and see how things pan out. The tempdb log file was also actually at less than 50% utilisation which fits with the idea it expanded under load this morning and has retained that space since.
I'm going ahead on the basis that the size it's grown to is the size it needs to be, monitor this size in future and make sure there's room for growth on whatever drive it's on. As suggested by some here I'll look into what is executing as the temp log expands and see if anything can be tweaked in there. I'll also keep an eye on those io performance counters to see if something needs dealing with.
There was one more additional interesting section titled 'Upgrading to SQL Server 2005' which indicates that tempdb is used for more things in 2005 than 2000 (both new features, and existing features which previously didn't use tempdb). I've only recently upgraded to 2005 so this could be part of the reason this has suddenly become an issue. I don't remember seeing this anywhere else with reference to upgrading to 2005 though, which is a bit of a pain.
Some SQL query or stored proc is doing bad things -- the only thing you can do is profile the tempdb to catch the "Database:Log File Auto Grow" event, and when that happens, use profiler and queries against tables like sysprocesses to find out what the bad process is.
Unfortunately, it's down to old-fashioned detective work to trace the rogue process.
Have you tried resizing the tempdb log file(s) with DBCC SHRINKFILE() ? If so, how long does it take to get from [very small value] to 45GB or more?
This is most likely caused by an out of control Cross Join query. Your best bet is to use Profiler to find it and then fix it.
The other way to find it is to restrict the size of the TempDB log file and then wait to see what query fails. However, I'd bet that it is already failing and no one is telling you.
If you restart the SQL engine the file will be set to the initial size. You should put a max size on all your autogrow files.