SQL Server Agent Job Running Slow

sql-server sql-server-2005 sql-agent-job sql-agent
Prateek picture Prateek · Aug 16, 2011 · Viewed 10.5k times · Source

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

Bernhard Kircher picture Bernhard Kircher · Aug 17, 2011

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.

Related questions

Add a column with a default value to an existing table in SQL Server

How can I add a column with a default value to an existing table in SQL Server 2000 / SQL Server 2005?

Read More
Insert results of a stored procedure into a temporary table

How do I do a SELECT * INTO [temp table] FROM [stored procedure]? Not FROM [Table] and without defining [temp table]? Select all data from BusinessLine into tmpBusLine works fine. select * into tmpBusLine from BusinessLine I am trying the same, but …

Read More
SQL query to select dates between two dates

I have a start_date and end_date. I want to get the list of dates in between these two dates. Can anyone help me pointing the mistake in my query. select Date,TotalAllowance from Calculation where EmployeeId=1 and Date …

Read More