Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase

sql server 2005 issues with nightly jobs Expand / Collapse
Posted Wednesday, March 6, 2013 12:01 AM


Group: General Forum Members
Last Login: Friday, March 27, 2015 7:19 PM
Points: 21, Visits: 184

I am just starting to learn about these things, and i was hoping someone could give me some pointers to what i should be looking at. Im running SQL Server 2005, have a nightly download from a system that takes several hours, after that is done there is a SQL job with several steps that run. Normally, this process finishes before 7AM, but lately its been taking really long, and i am lucky if it finishes before noon. Also, during the day there are often timeouts, and i noticed that it has problems when multiple sql jobs are running at a time.

Can anyone give me some suggestions to what could possibly be wrong, or where i can start looking?

On a sidenote, i had the same issue last year, but we moved our sql server to a new server and it has been running fine until now. I also restarted the server just in case.
Post #1427191
Posted Wednesday, March 6, 2013 9:42 AM



Group: General Forum Members
Last Login: Today @ 12:16 PM
Points: 2,855, Visits: 8,647
Could be any number of things. Fragmented indexes, blocking/locking, missing indexes, other applications grabbing CPU, any server changes recently ? code changes ? major data changes ? Bigger download & data needing processing ?
Read up on performance tuning.
Look at the job and try to determine which steps inside the job are taking longer than normal.

Post #1427503
Posted Wednesday, March 6, 2013 1:07 PM



Group: General Forum Members
Last Login: Tuesday, March 24, 2015 1:58 PM
Points: 1,972, Visits: 1,351
Have you looked at the Job History in the Activity Monitor? This should show how long each step (you stated your job had multiple steps) is taking as compared to say last month when the job was running on time.

What has changed lately with the download?

What has changed on the physical server itself?

Do you have any baseline numbers you can compare too from the past?

Chris Powell

George: You're kidding.
Elroy: Nope.
George: Then lie to me and say you're kidding.
Post #1427603
Posted Thursday, March 7, 2013 12:58 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, February 17, 2015 9:20 PM
Points: 1,060, Visits: 2,702
look at the failed jobs and look at the other jobs which runs around the same time. take running time also into consideration.

can you post more details like what is the job about and time taken, server configuration details.

Durai Nagarajan
Post #1427816
Posted Thursday, March 7, 2013 2:47 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, November 18, 2014 6:51 AM
Points: 232, Visits: 828
I had the similar issue job which was taking under three hours to complete started taking 48 hours or more ultimately I had to terminate(kill) the job as it was impacting server performance . After lot of analysis I found that particular job step was processing huge amount of data which caused job to overrun. In my case rewriting the query and introducing new indexes resolved it.

Talk to your Development team they would be able to test the particular query in CTC/UAT also as others suggested check for the indexes and tune them if need be. SQL server allows you to run jobs simultaneously as long jobs do not point to the same database/query it should be fine.

I would monitor the jobs in activity monitor or do sp_who2 to check for any block /locks while job is still running
Post #1427857
Posted Thursday, March 7, 2013 9:50 AM


Group: General Forum Members
Last Login: Monday, March 9, 2015 8:16 AM
Points: 414, Visits: 404
Here is a script that will show you the duration of each step for your jobs. this will give you a clue as to which step might be giving you the problem.

--shows duration by step for long running multi-step jobs

select as 'JobName',
s.step_id as 'Step',
s.step_name as 'StepName',
msdb.dbo.agent_datetime(run_date, run_time) as 'RunDateTime',
((run_duration/10000*3600 + (run_duration/100)%100*60 + run_duration%100 + 31 ) / 60)
as 'RunDurationMinutes'
From msdb.dbo.sysjobs j
INNER JOIN msdb.dbo.sysjobsteps s
ON j.job_id = s.job_id
INNER JOIN msdb.dbo.sysjobhistory h
ON s.job_id = h.job_id
AND s.step_id = h.step_id
AND h.step_id <> 0
where j.enabled = 1 --Only Enabled Jobs
--and = 'TestJob' --Uncomment to search for a single job
and msdb.dbo.agent_datetime(run_date, run_time)
BETWEEN '12/08/2012' and '12/10/2012' --Uncomment for date range queries
order by JobName, RunDateTime desc
Post #1428081
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse