February 29, 2008 at 6:42 pm
This is not an uncommon topic. After much reading, I manage to read a good one with
The following written by above link give me a good answer (I think):
Solution 1: Make the size of the TempDB log file bigger to start with. If anything it should decrease the frequency of this error popping up.
Solution 2: Create a performance condition alert on the TempDB Log File. Have SQL Server Agent Service check on the amount of data in the log file.... When it reaches say ... 90%, have it run a job to automatically increase the size of the log file.
The problem I encounter and my ADDITIONAL question is:
Our company outsourced the creation of a lovely application but the programmer they employ appears to be a first time developer. I am the person to support it after the project finishes. We run into tempdb log file full frequently every day (because our database grows). To give me a quick fix before the developer is back, I want to increase the initial log file size of tempdb to 4 GB ( grepping this slice is yet a problem at present). What other price we might be paying is I do that?
February 29, 2008 at 7:43 pm
Please post similar questions in 1 post.
One issue you need to deal with is that every time SQL Server is restarted, tempdb is recreated and it is recreated based on your model database. I don't think you want to waste 4GB of space on the log file for model.
Clearly your developer is leaving an open transaction so that the log file is not being shrunk on regular bases. DBCC OpenTran('tempdb') will show if there are any active open transactions in tempdb.
You can also try to force truncation to occur by manually setting a checkpoint:
use tempdb
Go
checkpoint [seconds to wait for checkpoint to be set]
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
March 2, 2008 at 10:42 am
It could also be lots of temp tables being filled.
However you can increase the size of tempdb and it will build this on startup, without affecting model. If you need more space than the defaults, allocate more space. tempdb sizing is an instance by instance proposition.
March 8, 2010 at 7:15 am
would there be any case where temdb is filled with temp transactions and space did not get released back after the completion of the job.
thanks
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply