sql job does not run

Question

sql job does not run

aruram

SSCrazy

Points: 2379
More actions
December 23, 2004 at 10:33 am

#183719

Hi
I am facing a pb running one of the sql jobs. Job A runs fine when it is scheduled to run separately on Sever A. However, i want it to run based on the successful completion of another job B on Server B and the way I do this is by enabling Job A on server A if job B on server B succeeds. Job A is succesfully enabled but does not run as per its schedule. Why is this happening ???
TIA

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

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

hoo-t SSChampion Points: 12713 More actions · Answer 1

Is the schedule also enabled? Is SQL Agent running?

I have the same type situation with homegrown logshipping. I accomplish this by updating a "control" table on Server B when Job A succeeds. Job B then checks the control table before doing any work.

Steve

aruram SSCrazy Points: 2379 More actions · Answer 2

Hi

Thanks for your suggestion. I will try using a control table but I am curious why the job does not run. The job is enabled and sql agent running. Is this a bug ?

Thanks

hoo-t SSChampion Points: 12713 More actions · Answer 3

There is a bug that affects job scheduling, but this doesn't sound like it. Just make sure that you are on the latest service pack and you'll be ok as far as known bugs are concerned (I'm assuming you are using SQL 2000).

How are you enabling the job? Are you running sp_update_job from Server A via a remote procedure call? linked servers? Have you verified that the job is enabled? Are the system times synchronized on the servers? If Server B's clock is ahead of Server A's, is it possible that the execution time has already passed by the time the job is enabled?

Steve

aruram SSCrazy Points: 2379 More actions · Answer 4

It isn't the system times as there is enought time gap between the 2 jobs, between the time it gets enabled and when it actually runs.

I am updating the sysjobs table and am setting the enabled field for the specifc job, via linked servers.

We are on sql2000 with the latest service pack.

The job does get successfully enabled and I have verified that. It even shows the next run date schedule but just doesnt run at that specified time !

hoo-t SSChampion Points: 12713 More actions · Answer 5

I think thats where your problem may be. You should never update the system tables directly. There are many "behind the scenes" things that you can't possibly take into account. Microsoft has provided a way of handling just about anything that you may want to adjust system tables for though. In this case, it is sp_update_job.

All you would need to do is execute the following on Server B:

USE msdb
EXEC sp_update_job @job_name = 'Job B',   @enabled = 1

I'll bet this will take care of your problem.

By the way, you could add a step to Job B to disable the job again. Just use the same code and set @enabled = 0 .

Steve

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 6

If you use the propre sp, things whill work fine.

This is because these procs also tell the running sqlagent to refresh its job-data.

I use it all the time when e.g. I schedule db-maintenance, the first thing I do is disable the incremental backup job plus leaving a notification.

I do it this way :

EXEC MSDB.DBO.sp_update_job @job_name = N'myserverLog_Incremental', @new_name = N'DBMaintenanceProcedure_Disabled - myserverLog_Incremental', @enabled = 0

the last step contains the reactivation of this inclemental backup jop

EXEC MSDB.DBO.sp_update_job @job_name = N'DBMaintenanceProcedure_Disabled - myserverLog_Incremental', @new_name = N'myserverLog_Incremental', @enabled = 1

Works as desinged

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

hoo-t SSChampion Points: 12713 More actions · Answer 7

Hey, alzdba,

I like that renaming of the job! Makes it easy to tell at a glance that its disabled!

Steve

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 8

IMO it is more important that we can see who or what disabled it. and that it is meanth to stay disabled.

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

Arnie Rowland Mr or Mrs. 500 Points: 515 More actions · Answer 9

There are two (2) things to enable. The job and the job schedule. Have you checked to determine if BOTH are being enabled?

Yelena Varshal SSC-Dedicated Points: 34286 More actions · Answer 10

Did you check Event Logs for the scheduled time or did you check the Job History? Is there any errors here? The question is, does the job attempts to start?

Regards,Yelena Varsha