Sql Server Agent

[Leighton21]

Hi all,

Hopefully a simple question. If I have a sql server agent job running which is scheduled for every minute and the server crashes or is offline due to maintenance, when the server agent starts again will the job catch up?? i.e. will the job run from the last scheduled time?

The reason I ask is that the particular job checks the scheduled time against a time in a user table and then runs a SP if they coincide. If the sql server agent does not perform a catch up and one of the times in the user table is missed I will run into a problem.

If there is no catch up operation does anyone have any suggestions on how I could achieve this

Thanks

 

[SajidAttar]

Hi..

I dont have any idea how your procedure processes the users table.. but if you are storing the datetime that the users table and using that value in your next process as start date then you should not be in trouble..



Regards,


"Dream not what makes your sleep a pleasure, but what makes you work
hard to achieve it and forget your sleep (untill you achieve it)." -- SJD

 

[JoeAtWork]

I would think that instead of (or in addition to) comparing times you would have some sort of flag that gets set when you have made the update. Then you could make your condition for doing whatever it does to any time earlier in the users table AND where the flag has not been set.

 

[Crowley16]

to answer you first question, SQL Server Agent will NOT retrospectively fire off "catch up" operations, and WILL resume from the current time

--------------------
Procrastinate Now!

 

[ousoonerjoe]

Could you not use a trigger to set the flag/bit field JoeAtWork mentions?

To take it a step further, can you not use a trigger to execute the process in question? I would have it set a flag to indicate completion and email you only when a failure occurs. If it's fired from the trigger, it should always fire as needed and only as needed and therefore becomes completely independent of the SQL Agent.

Someone correct me if I'm wrong, please.

"If I were to wake up with my head sewn to the carpet, I wouldn't be more surprised than I am right now.

 

[Leighton21]

Thanks Guys,

Not sure If I can do this with a trigger.

Let me give you a little more background. The table that has the date time in it is an effectual date. Users will put datetime entries in along with a theshold for an instrument. This could sometimes be six months in advance. so say if someone put in 80% as threshold and this value would take effect on 01/06/2008 07:00. The stored procedure should fiire every hour and then when 01/06/2008 07:00 occurs then the threshold that is configured will update throughout the database. So I dont think a trigger would work here since it does not necesarily fire at the date entered

Regards

 

[Leighton21]

Also the reason I was wondering if a "catch up" occured was if perhaps the server was down for maintenance or some other stoppage. I wouldnt want a effectual threshold to be missed. i.e. If the server was down at 01/06/2008 07:00 and the SP did not update the threshold an incorrect value would be produce incorrect QC reADINGS

Cheers

 

[ousoonerjoe]

a unix deamon would be perfect for this.

In that case, i would create a new column [Processed] AS Bit, and set that flag to TRUE once the record is processed. It would also add a bit of auditing for you so you can do a pull on all records that have NOT been processed.

"If I were to wake up with my head sewn to the carpet, I wouldn't be more surprised than I am right now.

 

[Leighton21]

Thanks all