tempdb log filled error

This is sql 2000..
I ran a script on a client database to update some fields. The script joins 2 tables and sums up a column from TB2 and updates TB1.

One table has 1786967 records and the other one has 1845936 records. The update took a long time to run, then gave 2 errors: one was deadlock, the other was tempdb transaction log is full error.

I ended up adding another clause to narrow down the resultset but dont want to do that in production.

tempdb's ldf file is 6912KB and the mdf is 284288KB. Doesnt seem large. Do I need to shrink the ldf file?
Who is Participating?
imitchieConnect With a Mentor Commented:
to perform your update to TB1, sql server has to first process your sums from TB2. this work is done in the tempdb, and because your update is SO intensive (sum a big table, hold results, update other big table) - the whole thing has to
1, be done in an implicit transaction (nothing can touch tb1 or tb2 while it is happening)
2, causes a major expansion of tempdb, even if only temporarily

you should consider
1, temporarily increasing tempdb allocation to insane levels (not the initial, just the max size). you can shrink it back down when update is done; and
2, doing it at a time when no one's on the system!
note: you may still blow the log for the database in use

another option is to break your updates into manageable chunks and perform a series of smaller updates
wshark83Connect With a Mentor Commented:
you can either shrink the file or truncate the log file for both your database and tempdb
James MurrellConnect With a Mentor Product SpecialistCommented:
clear the tempDB space there are several  methods.
CamilliaAuthor Commented:
how could tempdb's tlog grow when it's that small and it's on Simple recovery??
James MurrellProduct SpecialistCommented:
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.