trapping jobs or threads that may be running

  • I have a job that is scheduled to run each night. Most nights it take 5-10 minutes to complete. Every now and again, it jumps to 100+ minutes to complete. As it's scheduled to run during off hours, that's not a problem. What bothers me is I cannot figure out what causes the time to jump so drastically. Any thoughts on how I can trap the information? It's a dedicated SQL 2K, sp3 server. Thanks.


    Terry

  • It sounds like a blocking issue. It could also be resource contention. Set up a job that runs frequently that monitors blocking. alert you when blocking happens.

  • If you have couple of steps in the job then narrow down as which step is taking the time, how many time the retry option is set up in the job. It could be a possible the job failed but the retry option keep making the job


    Kindest Regards,

    Amit Lohia

  • What type of job is it?

  • I would suggest running the SQL profiler over night. If your script inlcudes storedprocedure calls then add from the events tab Storedprocedure:

    RPC:Starting and RPC:Completed

    SP:Starting and SP:Completed

    SP:StmtStarting and SP:StmtCompleted

    you will also want to add TSQL from the events tab  SQL:BatchStarting and SQL:BatchCompleted.

    Finally add from Locks:  Lock:Timeout

    Make sure you have the duration column selected on the data columns tab.

    When you run this profile it will generate a large quantity of data but will allow you to determine which parts of your script are running slowly (the duration column) Also if there is an issue with locking then Lock timeouts may show somthing useful.

    You may also want to set a filter so it only caputures data from a particular host but you may miss some other activity that is affecting your process.

    Regards

    Julian

  • What type of load will profiler add to the system?


    Terry

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply