Managing failed and missed jobs

  • Good day,

    I've been wondering how do people handle failed and missed jobs in which time information is crucial on SQL Server 2000.

    For example, when a job runs daily, but one day it either fails or is missed (due to e. g. downtime).

    How do you run such a job again?

    Do you edit its time variables, run it, then change it back again? Changing the server's time is not really an option, changing bigger packages isn't one either (due to time restraints).

    Is it possible to run a job in something like a 'time machine' that would enable one to execute a job as if it was a different day?

    Suggestions are most welcome 🙂

    Thanks.

  • I know you can set RETRY count (and interval?) for SQL Job

    but I don't think it makes sense logically to fix it and pretend it did run in the past (unless job expects a datetime parameter which you can manually input). Most jobs should be time-insensitive.

    Jobs like Log Shipping or TLOG backup can miss and not affect much as next successful run will catch up any missed entries

    For critical jobs on production, you can setup Alerts to Operators

    SQLServerNewbieMCITP: Database Administrator SQL Server 2005
  • How you handle a failed job really depends on what the job does. As Jerry said, some failed jobs can be ignored because they'll get caught up on the next scheduled run.

    You may want to manually run a job in some cases, which overrides the job's schedule. You just right-click on the job in Enterprise Manager and select "start job".

    Greg

  • Thanks for the replies guys.

    Yeah, I know about the retry parameter, but I'd rather avoid using it, because there are many jobs on the server we have and some of them are pretty resource intensive. Running them in paralell could do more harm than good.

    My question is mostly about daily jobs that have to run daily and provide data that need processing by e.g. the accounting department or customer notifications that can't be fully automated.

    Sure, I can run a job manually, but not if it gets noticed the day after the day should have ran on. That's the problem. There are also jobs that run once a week and so on and the problem there is similar, but the daily jobs are the most sensitive ones. Some of the weekly jobs also depend on the daily jobs.

    That's why I'm after a solution that would make running such missed/failed jobs as if it was a different day.

  • If your jobs just call a stored procedure to do it's work then you could add a parameter to the procedure which is the "rundate". If the parameter isn't supplied then it could default to now.

    If you want to run the job manually, then just run the stored procedure from a query passing in yesterdays date

    David

  • David (12/5/2008)


    If your jobs just call a stored procedure to do it's work then you could add a parameter to the procedure which is the "rundate". If the parameter isn't supplied then it could default to now.

    If you want to run the job manually, then just run the stored procedure from a query passing in yesterdays date

    David

    Unfortunately, most of the jobs contain raw queries, some of which use procedures and functions, but most of those procedures and functions aren't time-dependent, just the rest of the query content.

    It would be if one could assign a variable to a job (not in a query), it would be great, but so far, it seems I'll just have to be changing the queries temporarily.

  • can you provide an example?

  • David (12/5/2008)


    can you provide an example?

    No, sorry, I'm not allowed to do so.

    The queries I've looked at are mostly selects with a few joins and time columns that are compared to getdate() (less/more than, date extraction and such).

    It's not much of a problem when a few jobs are missed/failed, but if it's more, then it takes too long to either edit them or run the queries manually and paste them into excel/csv sheets.

    If I could fool sql server into thinking it's a different day, then it would be very nice. It would rock it that was possible for selected jobs.

    I guess it's not, but then again, I might be wrong (which is why I'm here).

    Thanks for the help so far 🙂

  • I think personally I would convert the sql in the jobs into stored procedures.

    Another option is to hold an override date in the registry and then use the undocumented xp_regread to extract it. I can see a few issues with this however...

    1. You will be using an undocumented, hence unsupported command

    2. What happens if you forgot to delete the registry setting when you are done.

    3. Care must be taken to ensure the registry setting is a valid date

    DECLARE @test-2 varchar(20)

    DECLARE @date datetime

    EXEC master..xp_regread @rootkey='HKEY_LOCAL_MACHINE',

    @key='SOFTWARE\Test',

    @value_name='DateToUse',

    @value=@test OUTPUT

    if @test-2 is null

    set @date = getdate()

    else

    set @date = cast(@test as datetime)

    SELECT @date

  • David (12/8/2008)


    I think personally I would convert the sql in the jobs into stored procedures.

    Another option is to hold an override date in the registry and then use the undocumented xp_regread to extract it. I can see a few issues with this however...

    1. You will be using an undocumented, hence unsupported command

    2. What happens if you forgot to delete the registry setting when you are done.

    3. Care must be taken to ensure the registry setting is a valid date

    DECLARE @test-2 varchar(20)

    DECLARE @date datetime

    EXEC master..xp_regread @rootkey='HKEY_LOCAL_MACHINE',

    @key='SOFTWARE\Test',

    @value_name='DateToUse',

    @value=@test OUTPUT

    if @test-2 is null

    set @date = getdate()

    else

    set @date = cast(@test as datetime)

    SELECT @date

    Thanks David, that looks good, not as neat as a 'time machine', but it'll do.

    Is there a way to avoid reading (and writing, for that matter) into the registry, e. g. via a custom global variable, if such a thing exists in SQL Server. I've tried searching for this and couldn't really find anything (except for this: http://weblogs.sqlteam.com/mladenp/archive/2007/04/23/60185.aspx ), so I thought you might know about another undocumented feature ;).

    I thought about creating something like a configuration table that would contain necessary values (not just time, if we needed it) myself, but it seems to be a little cumbersome. Hence the 'global variable' question.

    Thanks again 🙂

  • It looks like the global variable link just uses a custom table anyway.

    David

  • Yep.

    I'll stick to something like that then 🙂

    Thanks for the advice.

Viewing 12 posts - 1 through 11 (of 11 total)

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