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,
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.