July 18, 2006 at 3:26 pm
Hi All,
Maybe I'm being daft but here goes.
Run a stored proc in QA, runs for 30 minutes, generates large result set (150K rows) which nearly crashes my PC by filling the disk, but at least it works.
Now, I need to schedule this SP overnight. So, I have a stored procedure wrapper which does something along the lines of work out start and end dates to be passed as parameters to the SP, then builds up an 'isql' command string (isql -S blah -U sa -P etc...), assigns this to a variable and does EXEC (@Cmd). SP runs for 7 minutes and dies, out of tempdb space. Don't want to enable autoextend for tempdb as a workaround.
So why does this work via QA but not isql ? I had a hunch that it's something to do with the large result set getting cached in tempdb. Anyone got any thoughts ?
Ade
July 18, 2006 at 4:25 pm
QA is the front end that returns results based on the recordset properties that may specify that results are returned to the client after certain number of records are retrieved. ISQL is another client with different defaults and it is possible that a server-side cursor is created.
Regards,Yelena Varsha
July 21, 2006 at 1:43 am
Ade
I know this doesn't answer your question, but it may make your life easier. Why are you shelling out the SQL statement @Cmd to isql? Why don't you run it directly in your scheduled job?
John
July 21, 2006 at 4:24 am
It's because we calculate date parameters to be passed to the command, these are worked out in a stored proc, then we call isql...
I think I've answered the problem though, looks like it's an execution plan issue. Found out that our developers were passing dummy parameters to the SP that returned no results but generated a plan in the procedure cache, then the call it again wih the proper parameters that use the plan thats just been generated.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply