Prevent Error "log file for database ''tempdb'' is full"

Question

Prevent Error "log file for database ''tempdb'' is full"

j mangual

Ten Centuries

Points: 1197
More actions
July 26, 2007 at 9:34 am

#69314

Got the error above on a vendor database for the first time. SQL server version is 2000 SP4. The databse has been in the production for over 8 months now. Vendor has a nightly job that executes "dump tran tempdb with no_log".
At the time of the error, the free space on the OS Disk was 232G, the tempdb recovery model is set to Simple, the autoshrink on db option is not set up, datafile automatically grow option was on using the by percent of 10%.
The solution was to increased the size of the datafile to 500M and change the datafile automatically grow option to grow by Megabytes of 50M at at time.
Why the log din't grow automatically as it was set up to do it? Why can be done to avoid this error in the future? Any suggestions?

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

My guess is that tempdb can't go from 10 to 500 in a single transaction (not 100% sure of this).

The best practice I know about this is to see how big tempdb gets on normal use, then set it to grow to at least 2-3 times that size on start up. that way you'll rarely, if ever run out of room.

j mangual Ten Centuries Points: 1197 More actions · Answer 2

The initial size of tempdb is 400M so it could have grow as per needed. Maybe I'm missing something because between 400 and 500 there is not a huge difference.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

I know but IIRC, there's either a timeout or a limited amount of growth that can be done in a simgle transaction... I think this is what is happening but then again I'm not expert on that matter.

SQLBill SSC Guru Points: 51440 More actions · Answer 4

10% of 400 is only 40GB. Depending on how fast and large it needed to grow, it would have to expand three times to make another 100 GB of space. That might not have been fast enough for the transactions happening.

400 + 40 = 440 (10% is 44GB)

440 + 44 = 484 (10% is 48.4 GB)

484 + 48.4 = 532.4

-SQLBill

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 5

I think he meant 500 MB.

But I'd like to see a tempdb of 500 GB someday .

j mangual Ten Centuries Points: 1197 More actions · Answer 6

Does anyone out there backup the transaction log of the tempdb on a daily bases as a preventive maintenance task?