Tempdb Log Full Caused Slow Performance ? 1

[JohnBates]

hi DBAs and SQL Server gurus,

We had a serious slowdown in performance last night on our SQL Server 2000 machine.

During the slow period, we got this message....
"DESCRIPTION: Error: 9002, Severity: 17, State: 6
The log file for database 'tempdb' is full. Back up the transaction log for the database to free up some log space."


Well, you cannot backup tempdb's log file.
After I rebooted the server, the same processes that were running very slowly last night, now run fast.

The log size for tempdb was set to max 150MB. Today I increased it to 500MB.

Is there any way to resolve this condition, other than a restart of the server ?

Thanks for your ideas. John

 

[SQLBill]

Yes...you need to set the TEMPDB log to autogrow. Or you need to make sure the TEMPDB log is large enough for all transactions that might happen at one time.

If autogrow is set to on but the size is too small, you will have the same issue. This is because the log won't grow fast enough for the transaction(s).

-SQLBill

 

[JohnBates]

thanks SQLBill.

I already had Auto Grow ON for both data file and log file
on tempdb.

Space allocated for log was 150MB. But Grow by 1MB may have been too low. I changed it today to grow by 5MB and Space allocated to 500MB.

Do you think these changes will help to keep the log from becoming full? (we had not had this problem in 2 months, so my original settings were pretty good I guess)

thanks, John

 

[SQLBill]

Do I think these changes will keep the log from becoming full? It's hard (impossible) for me to say. I don't know how much data you are dealing with each day. My main database is 162+ GB. It only gets INSERTs and SELECTs. I have both the database and TEMPDB logs set to grow by 1000MB. Is that too big? Maybe, but I never have problems with the logs being full. And my logs don't have to grow several times per transaction. (In fact they only grow once a month).

Bottom line is: you as DBA needs to watch the growth and 'tweak' the sizes until you find that 'happy medium' where the growth of the logs match the needs of the transactions.

-SQLBill

 

[JohnBates]

thanks for your expertise SQLBill.
John