Post reply

Need script for number of times a job has failed in a week

  • February 21, 2016 at 10:52 pm

    #305011

    Hi All,

    Can any one provide script for number of times a job has failed in a week

    Need some custom queries. which can create a stored procedure that will count the number of times a job has failed in a week?

    Here is a sample of what we are looking for..

    Server: xyz

    Job Name Frequency of Failures

    job1 4

    job2 3

    need some scripts to accomplish this. Perhaps dump your records into a temp table each week. so have a table that resides on all servers and gather data in that table every day

    So table structure could be something like

    Date JobName Failure

    19feb16 job1 1

    20feb16 job2 1

    21feb job3 1

    And at the end just count number of failures for each count and create a SP that will spit out the following output

    Thanks
    Naga.Rohitkumar

  • February 21, 2016 at 11:20 pm

    #1860010

    Recommend you take a look at the msdb.dbo.sysjobhistory table. Follow your nose from there. You can do this.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • February 21, 2016 at 11:30 pm

    #1860011

    Hi jeff ,

    I have checked that msdb.dbo.sysjobhistory but its not giving the job name not status of the job when it failed

    we need the count for number of times a job has failed in a week?

    our requirement is

    Server: xyz

    Job Name Frequency of Failures

    job1 4

    job2 3

    need some scripts to accomplish this. Perhaps dump your records into a temp table each week. so have a table that resides on all servers and gather data in that table every day

    So table structure could be something like

    Date JobName Failure

    19feb16 job1 1

    20feb16 job2 1

    21feb job3 1

    And at the end just count number of failures for each count and create a SP that will spit out the following output

    Thanks
    Naga.Rohitkumar

  • February 22, 2016 at 1:41 am

    #1860020

    Join sysjobhistory with sysjobs on job_id to get the job name.

    You don't want us to do the all the work, do you?

    -- Gianluca Sartori

  • February 22, 2016 at 4:54 pm

    #1860234

    naga.rohitkumar (2/21/2016)

    Hi jeff ,

    I have checked that msdb.dbo.sysjobhistory but its not giving the job name not status of the job when it failed

    we need the count for number of times a job has failed in a week?

    our requirement is

    Server: xyz

    Job Name Frequency of Failures

    job1 4

    job2 3

    need some scripts to accomplish this. Perhaps dump your records into a temp table each week. so have a table that resides on all servers and gather data in that table every day

    So table structure could be something like

    Date JobName Failure

    19feb16 job1 1

    20feb16 job2 1

    21feb job3 1

    And at the end just count number of failures for each count and create a SP that will spit out the following output

    Like I said in my previous post....

    Jeff Moden (2/21/2016)

    Recommend you take a look at the msdb.dbo.sysjobhistory table. [font="Arial"]Follow your nose from there. You can do this.[/font]

    If we write the whole shebang for you, then 1) you'll learn nothing and 2) you won't be able to support it. Give it a shot. "You can do this". 😉

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • February 23, 2016 at 8:54 am

    #1860444

    naga.rohitkumar (2/21/2016)

    Hi jeff ,

    I have checked that msdb.dbo.sysjobhistory but its not giving the job name not status of the job when it failed

    we need the count for number of times a job has failed in a week?

    our requirement is

    Server: xyz

    Job Name Frequency of Failures

    job1 4

    job2 3

    need some scripts to accomplish this. Perhaps dump your records into a temp table each week. so have a table that resides on all servers and gather data in that table every day

    So table structure could be something like

    Date JobName Failure

    19feb16 job1 1

    20feb16 job2 1

    21feb job3 1

    And at the end just count number of failures for each count and create a SP that will spit out the following output

    Where are you getting stuck?

    This page will tell you what you need to know for fields from dbo.sysjobhistory: https://msdn.microsoft.com/en-gb/library/ms174997(v=sql.100).aspx

    Join to dbo.sysjobs to get the job name.

    HTH

    LinkedIn

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

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