9

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.

Improve this question
1
  • You should look at (and post?) the SQL for those reports then.
    – RBarryYoung
    Sep 2, 2009 at 12:19

8 Answers 8

Reset to default
3

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?

Improve this answer
2
  • 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
    Sep 3, 2009 at 12:37
  • 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.
    – Robin
    Sep 4, 2009 at 7:46
4

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.

Improve this answer
1
  • 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.
    – Your_comment_is_not_funny
    Jan 28, 2016 at 22:02
1

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.

Improve this answer
12
  • 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.
    – Robin
    Sep 2, 2009 at 11:17
  • 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.
    – joeqwerty
    Sep 2, 2009 at 11:35
  • 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.
    – RBarryYoung
    Sep 2, 2009 at 12:08
  • 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
    Sep 2, 2009 at 12:20
  • My assumption was that if there were multiple templog files, they wouldn't grow to such a massive size.
    – joeqwerty
    Sep 2, 2009 at 12:21
1

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.

Improve this answer
0

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.

Improve this answer
0

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.

Improve this answer
0

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?

Improve this answer
1
  • 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.
    – Robin
    Sep 2, 2009 at 12:19
0

I could shrink it using

USE tempdb;
GO
DBCC SHRINKFILE (templog, 1);
GO
Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .