Home / server / Questions / 61957
Accepted
Robin
Asked: 2009-09-04 05:03:16 +0800 CST

Is it ok to limit the size of templog in SQL Server 2005?

  • 772

I've a problem I'm investigating whereby my tempdb logfile grows out of all proportion to anything which would seem sensible. This morning it was over 10x the size of the data files. However, the database continues to function fine and the only limit on the size of the templog seems to be the size of the disk it's on.

My question then, would it be safe for me to limit the size of the templog, I have heard you shouldn't limit the size of tempdb as it is critical for work which needs to be done within sql server. But, the fact the log file grows just to the size allowed by disk suggests I could limit it without breaking anything. Is this the case? and If so, is there a lower bound (ie the combined size of the data files) I should not limit it below?

Btw, I realise that this is not a solution to the issue of the log growing, that is being investigated but may not be solved soon.

Cheers,

sql-server-2005 tempdb

2 Answers

  1. Best Answer

    All you are going to do by limiting the size of the log is cause a failure when whatever is growing the log hits the limit. (Will error out and probably roll back the transaction).

    Actually, might be a usable way to see what is growing the log so large! :) Although an even better way would be to use Perfmon to monitor the SQL:Database:Log Used (kb) counter to see when it is growing, and correlate that to other activity on the server.

    • 1

  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.

    Hope this helps.

    • 1