SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


sql server 2005 issues with nightly jobs


sql server 2005 issues with nightly jobs

Author
Message
jenny 12957
jenny 12957
SSC Rookie
SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)

Group: General Forum Members
Points: 31 Visits: 188
Hi,

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.
homebrew01
homebrew01
SSCarpal Tunnel
SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)

Group: General Forum Members
Points: 4771 Visits: 9108
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.



Meet George Jetson
Meet George Jetson
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2943 Visits: 1395
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.
durai nagarajan
durai nagarajan
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1603 Visits: 2775
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.

Regards
Durai Nagarajan
Sqlsavy
Sqlsavy
Old Hand
Old Hand (378 reputation)Old Hand (378 reputation)Old Hand (378 reputation)Old Hand (378 reputation)Old Hand (378 reputation)Old Hand (378 reputation)Old Hand (378 reputation)Old Hand (378 reputation)

Group: General Forum Members
Points: 378 Visits: 862
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
Noetic DBA
Noetic DBA
SSC-Addicted
SSC-Addicted (451 reputation)SSC-Addicted (451 reputation)SSC-Addicted (451 reputation)SSC-Addicted (451 reputation)SSC-Addicted (451 reputation)SSC-Addicted (451 reputation)SSC-Addicted (451 reputation)SSC-Addicted (451 reputation)

Group: General Forum Members
Points: 451 Visits: 420
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
j.name 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 j.name = '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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search