October 22, 2008 at 10:02 am
First of all, I'm an SQL newbie so my terminology my be incorrect.
We have a job which when run takes around 45 minutes. That is, when we run it live. If we schedule it to run via the SQL Server Agent, it takes anywhere from 13 to 62 hours! We have considered that the time could be conflicting with backups or something of that nature so have tried scheduling it at different times. This has not helped.
Are there any known problems with the scheduler which could cause this sort of behavior? Is there anything else in particular we could look for to determine what is causing this?
Thanks,
Larry
October 22, 2008 at 10:10 am
No issues I'm aware of. You might try profiling/tracing this when it runs both ways and look for differences.
Is security the same? What does the job do?
October 22, 2008 at 8:27 pm
The ANSI settings in agent are different than in an interactive session, that's another thing I can think of.
It would have to be the perfect storm, but things like returning rowcounts and using a different execution plan might be coming into play.
Running a trace like Steve suggested is a good way to get to the bottom of that, too.
At the very least you could capture your own ANSI settings and embed them in your script to eliminate those differences as part of the problem.
You could also embed the script in SSIS/DTS and see if that helps.
One final bit of advice would be to start looking at peformance alerts with both perfmon and SQL agent.
Good luck
~BOT
Craig Outcalt
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply