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 (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)

Group: General Forum Members
Points: 47 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
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11618 Visits: 9222
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
Hall of Fame
Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)

Group: General Forum Members
Points: 3838 Visits: 1398
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
Hall of Fame
Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)

Group: General Forum Members
Points: 3299 Visits: 2784
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
SSC Eights!
SSC Eights! (833 reputation)SSC Eights! (833 reputation)SSC Eights! (833 reputation)SSC Eights! (833 reputation)SSC Eights! (833 reputation)SSC Eights! (833 reputation)SSC Eights! (833 reputation)SSC Eights! (833 reputation)

Group: General Forum Members
Points: 833 Visits: 868
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
Mr or Mrs. 500
Mr or Mrs. 500 (535 reputation)Mr or Mrs. 500 (535 reputation)Mr or Mrs. 500 (535 reputation)Mr or Mrs. 500 (535 reputation)Mr or Mrs. 500 (535 reputation)Mr or Mrs. 500 (535 reputation)Mr or Mrs. 500 (535 reputation)Mr or Mrs. 500 (535 reputation)

Group: General Forum Members
Points: 535 Visits: 422
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