August 18, 2005 at 1:43 pm
I don't understand why this is happening during an INSERT statement. Settings:
1.Tempdb data and log files set to autogrow (backed up nightly)
2. 273GB free on disk where tempdb resides.
Is it possible that if the log file size is too small that, even with autogrow, the need for diskspace "overwhelms" the ability of autogrow to supply it?
TIA,
Bill
August 18, 2005 at 2:30 pm
Can you send back the result of following commands? Please also post your insert statement.
use tempdb
go
sp_helpfile
go
dbcc sqlperf(logspace)
You don't have to backup the tempdb at all.
August 18, 2005 at 2:45 pm
I agree with Allen. There's no need to backup tempDB as it's recreated every time you restart SQL Server so it's just needless noise to you right now.
What is the autogrow setting on your log? I have seen and heard of cases where either the log or data file was set to grow in small (like 1 megabyte) increments and sometimes the speed of the query will over-run SQL Server's ability to add space to the tempDB files. If you've got plenty of drive space as you say, try changing this setting to 25 or 30% rather than using megabyte increments.
My hovercraft is full of eels.
August 18, 2005 at 2:53 pm
>>>
I have seen and heard of cases where either the log or data file was set to grow in small (like 1 megabyte) increments and sometimes the speed of the query will over-run SQL Server's ability to add space to the tempDB files
>>>
Yes, that is exactly what was heppening to us! The log file was sized too small. I increased its size substantially and the problem vanished!
Thanks to all for responding!
Bill
August 19, 2005 at 2:02 am
Hi - at what stage should you be worried that the temo DB is too big?
My main database returns a log space used of 43.143139% - should I be doing something to bring this down a little?
Thanks, Mark
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply