Good to hear you've got a successful completion

Grey matter has churned over sufficiently ...

Statistics are...

I'd also bet that the "sfbUpdateProductSalesSummary" SP is badly in need of some indexes, or maybe updating stats for existing indexes.

Obviously my grey matter isn't ticking over hard enough

Given that "TCSDailyNumbersIntoConfig" reports the failure, I'm betting that it's the "DROP TABLE #SizeSalesSummary" statement...

Just to make sure we're on the same page

Procedure sfbUpdateProductSalesSummary always returns failure in SQL Agent

Procedure TCSDailyNumbersIntoConfig pegs the CPU at 100%

Correct ?

Stranger and Stranger ...

Put a couple of PRINT statements in the procedure as status messages. These will show up in the output file.

The short answer, not necessarily.

The MSDN topic and its related topics will outline how SQL uses memory.

You shouldn't have a query that pegs the CPU at 100% for that long. Must be runnning really badly for that to happen. How many rows get added to the...

Hmmm ... I can understand how you can be frustrated with this. What you have a first look is not much help as the specific failure message from the procedure...

Posting the query might help us guide you towards ensuring the indexes you have are appropriate.''

Have you set the query wait time using sp_configure?

Also, check this KB

http://msdn2.microsoft.com/en-us/library/aa937573(SQL.80).aspx

How about providing the SP's and maybe the exact errors that you're receiving. Then we could aid you further without doing too much guesswork

Firstly, posting images doesn't work in this forum so try to list the dialog setting in text.

1) A 'straight datapump' is basically a table to table copy. No transformation of...

Is this a straight datapump copying data directly into the destination table? If not, eliminate any processing during the datapump phase. Do any data manipulation via stored procedures after the...

If your main concern is performance the a good rule of thumb is to adhere to standard T-SQL constructs wherever possible. Introducing additonal functions will only add to the overhead...

That is normal behaviour. SQL Server will consume as much memory as it needs, mostly for caching data. It will hold onto that memory until the operating system demands it...