System Load Issue(Script Crashes at Drop table)

Hello,

We have a script that ran just fine during system testing, but in production it crashes! Here is exactly where it crashes:

Step 1 - move from 1 category to another

Step 2 - move transaction data for the month (approx 200k) from one app to another

Step 3 - Allocations

Step 4 - move allocated data back to the first app

After reading the log file, which usually is my favorite night time reading, I realized that it crashes during Step 3. Here are Crticial points

Its creating temp table for Dim Members due to over 10000 members

Then it moves on to the what portion

During what portion, it times out. Right when its supposed to Select WHAT from #WHAT_972346 table. The very last line in the log file is drop table #972346.

So Is this some kind of Temp Table size issue? Any way to increase it? or am I missing something here?

Thanks.

Edited by: Zack Thacker on Jan 10, 2012 12:58 AM