Martin Schoombee (5/4/2015)
Pretty far behind on service packs.As Eirikur suggested and you've confirmed, AWE should be enabled. You should also ensure that the "Lock Pages In Memory" permission is assigned to the service account.
You could also reduce the max memory setting for SQL Server a little. Other than that, there's not much more you could do if there are no other processes consuming memory during your process's execution. If you still encounter errors, the data set is just too large to be stored in memory and you'll have to try alternative methods to reduce that load.
Martin, thanks for your reply.
I will suggest that the server be updated with the latest service packs. (I do not have control over the servers)
I believe that the ultimate solution is moving to 64bit OS. Until that can be schedule and setup, I began splitting the lookup into 2 lookups. I added a where clause to the queries on my larger tables to split the rows in the set in half. This seems to be working at the moment. However, it has increased the runtime from 30 minutes to 2 hours. It is still better than using the Partial Cache option. That option ran ALL DAY regardless of the amount of memory I set.
All the response to my question were helpful, but I am marking your response as the solution. The only workable approach was to reduce the data load. (Split the lookup into two)