SQL Server Agent Job Running Slow

Question

I am executing a stored procedure using SQL Server Agent Job in SQL Server 2005.

This job was running fast until yesterday. Since yesterday this job is taking more than 1 hour instead of 2 mins.

I executed the stored procedure in SSMS, it just took less than 1 minute to execute.

I could not figure out why it is taking more than 1 hour when executed as a SQL Server Agent job?

Answer 1

After some time commenting and assuming that the SP performs with the same input parameters and data well when executed in SSMS, I finnaly think I can give a last tip:

Depending on what actions are performed within the SP (e.g. inserting/updating/deleting a lot of data within a loop or cursor), you should set nocount on at the beginning of your code.

set nocount on

If this is not the case or does not help, please add more information, already mentioned in the comments (e.g. all settings of the Job and each Jobstep, what has been logged, what is in the Jobhistory, check SQLerrorlogs, eventlogs,....). Also take a look at the "SQL Server Logs" maybe you can gather some info here. Also a look into the Application/System eventlo of the Databaseserver is always a good idea. To get a basic overview you can use the Activitymonitor in SSMS, by selecting the Databaseserver and selecting "Activity monitor" from contextmenu and search for the sql agent.

My last try would be to try to run a sql trace for the agent. In this case you would start a trace and filter e.g. by the user that the SQLAgent Service runs. There are so many options you can set for traces, so I would recommend to google for it, search on MSDN or ask another question here on stackoverflow.