Managing failed and missed jobs

Question

Managing failed and missed jobs

slapo

Old Hand

Points: 335
More actions
December 4, 2008 at 2:09 am

#202466

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.

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

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

Jerry Hung SSChampion Points: 12908 More actions · Answer 1

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

Greg Charles SSC-Forever Points: 45403 More actions · Answer 2

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

slapo Old Hand Points: 335 More actions · Answer 3

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.

David Betteridge Hall of Fame Points: 3264 More actions · Answer 4

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

slapo Old Hand Points: 335 More actions · Answer 5

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.

David Betteridge Hall of Fame Points: 3264 More actions · Answer 6

David Betteridge

Hall of Fame

Points: 3264

December 5, 2008 at 8:36 am

#908372

can you provide an example?

slapo Old Hand Points: 335 More actions · Answer 7

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 🙂

David Betteridge Hall of Fame Points: 3264 More actions · Answer 8

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

slapo Old Hand Points: 335 More actions · Answer 9

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 🙂

David Betteridge Hall of Fame Points: 3264 More actions · Answer 10

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

David

slapo Old Hand Points: 335 More actions · Answer 11

Yep.

I'll stick to something like that then 🙂

Thanks for the advice.