My templog.ldf is huge (45gb), What if anything should I do?

Question

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.

Solution 1

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?

Solution 2

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.

Solution 3

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.

Solution 4

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.

View more solutions

Comments

[Rufflewind]

You should look at (and post?) the SQL for those reports then.

[Robin]

yep, it's set to simple recovery, I was just double checking that when I wrote the post, then forgot to include it in the above.

[joeqwerty]

I just checked our templog.ldf files and they're about 60MB. We have one tempdb file for each processor in the server. Have you tried creating additional files for the tempdb? The recommendation is to have one file for each processor (including the hyperhtreading processors). Our server has two dual core CPU's with hyperthreading so we have 8 tempdb files.

[Rufflewind]

That's the recommendation for SQL Server 2000. the recommendation for 2005 is considerably less than that. Either way, it has no effect on the total space once you get beyond the default size.

[Robin]

Please see edit 2 above for details of how quickly the file grows. Note this is based on a restart after office hours ahead of the next day and the mentioned scheduled reporting. The fact that it grows gradually over a couple of hours suggests to me it's not one badly behaving process but a combination of all the simultaneous load. Possibly the size it grows to is just the size it needs.

[joeqwerty]

It's another case of contadicting information. This article for SQL 2005 recommends a one to one ratio of files to cpu cores: msdn.microsoft.com/en-us/library/ms175527(SQL.90).aspx While this article for SQL 2008 recommends the same thing: msdn.microsoft.com/en-us/library/ms175527.aspx

[joeqwerty]

My assumption was that if there were multiple templog files, they wouldn't grow to such a massive size.

[Rufflewind]

Yep, the first article was written as the same time that 2005 was released when they did not know for sure if the 2005 fixes would really correct the problem in 2000 or not. Plus, it contains several incorrect statements (like that this problem has anything to do with IO bandwidth, it doesn't, it's a CPU mutex problem having to do with creation and allocation of temp objects, that's why the number is tied to CPUs). Within a year of this article, PSS and later all of MS was downgrading the recommendation to about half of that.

[Rufflewind]

The 2009 article is obviously just a cut and paste carry-forward of the 2005 article, preserving the same errors. The fact is that the 2005 fixes all but eliminated the problem and virtually all but the most hyperactive sites can run just fine with only one or two temp.mdfs, (and this is about the mdf's, not the ldf's) This has been written about and blogged about ad-nasuem by the likes of MS-PSS, Paul Randal, etc.

[Rufflewind]

Multiple templog files won't have any affect on the total size.

[Rufflewind]

Here's Paul Randall's article on it (by far the most comprehensive): sqlskills.com/BLOGS/PAUL/category/tempdb.aspx, not in particular item (5).

[Rufflewind]

oops, "Note" in particular item (5).

[joeqwerty]

Thanks much for clearing that up and pointing out the article.

[Robin]

We're looking further into this now, I'll post the results when I've got something conclusive. It currently looks like one report crashes out but doesn't release it's connection. I think then others are going through but causing the log to expand due to the earlier incomplete transaction.

[Robin]

Thanks for the answer, My problem is pretty definitely down to a crashing report now, I've limited the size the templog can grow to. I have seen the runaway growth in conjunction with a failing report not committing a transaction. I'm not sure if there's anything particularly bad in the report sql as they ran fine under SQL Server 2000 but I'll investigate what they're up to as well.

[Rufflewind]

Note: this is the correct link to the article: sqlskills.com/blogs/paul/misconceptions-around-tf-1118

[Your_comment_is_not_funny]

This was the solution on a poorly configured SQL 2000 instance I inherited where all the data and log files were on a single disk. We can't add storage so I had sized the file based on usage and have a job that runs a checkpoint every 30 minutes.