October 26, 2011 at 9:06 am
Our platform is sql server 2005 and job contains multiple steps. This job is kind of critical to finish on time since client expects data after this job completes. Recently, one of the step took 40 mins (around 2.45 am) cuasing the sql job to finish after the SLA time. I thought this step itslef had problem but the day after this step ran fine and another step (step next to it) took 40 mins (around 2.45 am)...It seems like something else is going on in the server around 2.45am...
How do I look what happened in the server around 2.45 am?
Thanks for your reply.
October 26, 2011 at 9:12 am
I would think of a blocking situation causing many waits for your jobstep to proceed.
If you don't have a blocking sampler or monitoring app, this is hard to prove 100%.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
October 26, 2011 at 9:15 am
One thing I did find though is there is a batch process to zip the files that runs around that time that usually takes around 20 mins...Would this be causing issue?
October 26, 2011 at 9:20 am
This monitors jobs & blocking.
Free 14 days trial + available 14 days extension
October 27, 2011 at 4:27 pm
Have you ruled out any long executing SQL Agent jobs? Using SQL Server Management Studio, SQL Agent, Jobs, you can right click on each job to "View History" and see when the jobs started and how long they executed. (A side note: I routinely track job execution times. This is sometimes the first indication of a problem developing on one of our servers.)
If examination of SQL Agent job history provides no useful information, I would set up a trace in SQL Profiler to filter on:
1. Queries that take a long time to execute.
2. Queries that have a high level of I/O. You can segregate the I/O by reads and writes.
3. Queries that have a high level of CPU utlization.
I've used all of the describled methods to determine what is happening on our servers both during and outside of normal work hours with some enlightening results.
For instance, we've been able to identify clients executing reports that downloaded many millions of records. This was causing tremendous resource stress on 2 of our servers. We responded in 2 ways:
1. We contacted the clients and asked them to limit the amount of data they requested.
2. I wrote a SQL Agent job that terminates any report process that executes for over 10 minutes. In our business, no one can realistically use that much data from one report.
Other times, I've been able to identify conflicting database maintenance and application jobs and alter their schedules to be mutually exclusive.
Just trying to give you some ideas.
LC
November 3, 2011 at 9:55 pm
A long running job can have many causes. A few below:
1) There may be any blocking that caused the job to wait for a resource.
2) If there is any other process or job running for a long time and in the same time, you need to check the job even if it is not blocking. Either change the time of the jobs or tune the job.
3) Check whether your job is using tempDB. Also is there any other job running the same time is using tempDB. Also check whether the jobs are filling the tempDB. Check the error logs for TempDB got filled up warning messages.
4) Check whether your job or any other job running in the same time is filling up the transaction log file. Check in the error log of the server for "Transaction Log full".
Hope this will help you.
Thanks.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply