Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2014
    Posts
    268
    Provided Answers: 6

    Answered: SQL Server Agent

    My end game is to automate some of my monthly queries in a Job in SQL Server Agent.

    Right now I have two metric tables. One table is the name and comment with the PK. The secondary table is attributes/detail, such as reporting month, target and actuals.

    I am currently running all different types of queries to get the aggregates. I'd like to get these into a job so it would run automatically and update the reporting table.

    Would you recommend making one step or multiple steps for each query? I am trying to use an intelligent approach to begin to load the tables.

    Any recommendations on an approach?

  2. Best Answer
    Posted by Pat Phelan

    "I would recommend multiple steps. At some point, things will fail for some reason. When SQL Agent emails you a message about a failure, it includes the step name as part of the message. If you break your process into logically related "chunks" and name the steps that implement those chunks appropriately, the failure message will tell you a LOT about what went wrong, where, and maybe even why. If you only have one job running, this isn't critical but I oversee thousands of jobs running for many clients so that information saves me hours of work every month.

    A happy side-effect of breaking the job into many pieces is that you only have to deal with the step that fails, not the whole beast. This can make problem solving a bunch easier.

    Depending on what went boink and why it flopped, you may also be able to restart a 43 step job at step 37 instead of having to restart it at step 1. This isn't always the case, but it is certainly a happy coincidence when you have users that are going to come online in an hour and a restart in the middle will cause them zero downtime and a restart from the beginning will cost them nearly a full shift. Dealing with people who show up before dawn with torches and pitchforks is not my idea of a good time!

    -PatP"


  3. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I would recommend multiple steps. At some point, things will fail for some reason. When SQL Agent emails you a message about a failure, it includes the step name as part of the message. If you break your process into logically related "chunks" and name the steps that implement those chunks appropriately, the failure message will tell you a LOT about what went wrong, where, and maybe even why. If you only have one job running, this isn't critical but I oversee thousands of jobs running for many clients so that information saves me hours of work every month.

    A happy side-effect of breaking the job into many pieces is that you only have to deal with the step that fails, not the whole beast. This can make problem solving a bunch easier.

    Depending on what went boink and why it flopped, you may also be able to restart a 43 step job at step 37 instead of having to restart it at step 1. This isn't always the case, but it is certainly a happy coincidence when you have users that are going to come online in an hour and a restart in the middle will cause them zero downtime and a restart from the beginning will cost them nearly a full shift. Dealing with people who show up before dawn with torches and pitchforks is not my idea of a good time!

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  4. #3
    Join Date
    Oct 2014
    Posts
    268
    Provided Answers: 6
    I hear you Pat, (pitch forks sounds wonderful). :/

    Noted and I will break them out into steps. Thanks!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •