April 9, 2010 at 6:26 am
We've apparently had the same problem. In disabling jobs (jobs that restore backups down to our Dev server) during month end, the job gets disabled but then still runs as scheduled anyway. The decision was made that we had to disable both the job and the schedule to truly disable the job.
But are you saying that it's a cache issue?
EDIT: Nevermind. Re-read the thread and think I understand it now. That makes sense.
April 9, 2010 at 6:46 am
I believe it is.
I was directly updating the field enabled in the table msdb.dbo.sysjobs, setting it to 1 or 0, depending on whether I wanted to enable it or not.
Although the T-SQL updated the record it the job still run, as previously described.
To resolve the issue, I changed my process to use the stored procedure sp_update_job
for example:
exec sp_update_job @job_name = 'MyJobToDisable', @enabled = '1'
Jobs then enabled / disabled as expected.
_____________________________________________________________________________MCITP: Business Intelligence Developer (2005)
April 9, 2010 at 6:49 am
I'm going to have to do more research. I'm now being told this happens when disabling jobs through the GUI, not just the code.
April 9, 2010 at 7:53 am
Brandie
This definitely happens when disabling from the GUI (see previous post). The system SP runs another SP, sp_sqlagent_notify, which clears the cache for the job; again, see previous post. So you can either use the SP or duplicate what it does, but in either case it's a T-SQL code task, not something the GUI does well - and generally direct updates to system tables are bad, Dave-DJ, so the sp_update_job definitely wins out. My other fix was to disable the schedule - I guess if it's a shared schedule then that would stop any jobs using that schedule from running (although I've not tested that - I don't generally use shared schedules).
February 15, 2011 at 12:56 pm
simon-811452 (4/9/2010)
BrandieThis definitely happens when disabling from the GUI (see previous post). The system SP runs another SP, sp_sqlagent_notify, which clears the cache for the job; again, see previous post. So you can either use the SP or duplicate what it does, but in either case it's a T-SQL code task, not something the GUI does well - and generally direct updates to system tables are bad, Dave-DJ, so the sp_update_job definitely wins out. My other fix was to disable the schedule - I guess if it's a shared schedule then that would stop any jobs using that schedule from running (although I've not tested that - I don't generally use shared schedules).
This is what i am exactly going through. I have disabled bunch of jobs through GUI (like selected 5 jobs together and right clicked, disabled) and renabled the same way. After re-enabling the jobs didn't run as per the schedule. However the jobs did run after i disabled-re-enabled the jobs "through script". Can you please say what is the best way to avoid this kind of situation? Thanks
February 15, 2011 at 1:29 pm
What service pack are you on ? Sometimes these odd problems occur in early versions, and are fixed in later SPs.
February 15, 2011 at 1:41 pm
homebrew01 (2/15/2011)
What service pack are you on ? Sometimes these odd problems occur in early versions, and are fixed in later SPs.
sql 2005 stnd edition 64 bit with SP3 + CU10.
February 15, 2011 at 2:15 pm
sp_update_job is definitely the way - the GUI disable and updating the table directly both seem to cause failures.
February 15, 2011 at 2:48 pm
simon-811452 (2/15/2011)
sp_update_job is definitely the way - the GUI disable and updating the table directly both seem to cause failures.
OK if i follow this correctly you mean to say the GUI is running the mentioned below:
i) EXEC msdb.dbo.sp_update_job @job_id=N'5bd63372-c981-4cef-b18f-2db4b464ee43',
@enabled=0
ii)UPDATE msdb.dbo.sysjobs
SET name = @new_name,
enabled = @enabled,
description = @description,
start_step_id = @start_step_id,
category_id = @category_id, -- Returned from sp_verify_job
owner_sid = @owner_sid,
notify_level_eventlog = @notify_level_eventlog,
notify_level_email = @notify_level_email,
notify_level_netsend = @notify_level_netsend,
notify_level_page = @notify_level_page,
notify_email_operator_id = @notify_email_operator_id, -- Returned from sp_verify_job
notify_netsend_operator_id = @notify_netsend_operator_id, -- Returned from sp_verify_job
notify_page_operator_id = @notify_page_operator_id, -- Returned from sp_verify_job
delete_level = @delete_level,
version_number = version_number + 1, -- Update the job's version
date_modified = GETDATE() -- Update the job's last-modified information
WHERE (job_id = @job_id) --- And this is causing the issue?
February 15, 2011 at 7:00 pm
update..from the previous post which i mentioned step ii is also part of step i. My question is same now, what is the difference between GUI and running through script.I just see this one proc " sp_get_composite_job_info" not part of sp_update_job.
February 16, 2011 at 4:17 am
The problem is, we don't know what the problem is. Both GUI and script should work. GUI is not working properly, however. So stick with the script or, as I do, do it through the GUI and run the update sproc afterward.
February 16, 2011 at 4:34 am
sqldba_icon (2/15/2011)
simon-811452 (4/9/2010)
BrandieThis definitely happens when disabling from the GUI (see previous post). The system SP runs another SP, sp_sqlagent_notify, which clears the cache for the job; again, see previous post. So you can either use the SP or duplicate what it does, but in either case it's a T-SQL code task, not something the GUI does well - and generally direct updates to system tables are bad, Dave-DJ, so the sp_update_job definitely wins out. My other fix was to disable the schedule - I guess if it's a shared schedule then that would stop any jobs using that schedule from running (although I've not tested that - I don't generally use shared schedules).
This is what i am exactly going through. I have disabled bunch of jobs through GUI (like selected 5 jobs together and right clicked, disabled) and renabled the same way. After re-enabling the jobs didn't run as per the schedule. However the jobs did run after i disabled-re-enabled the jobs "through script". Can you please say what is the best way to avoid this kind of situation? Thanks
I just realized this was an old thread. Sqldba_Icon, given that you have your own thread on this problem, could you please post all your questions and responses there, not here? You're just going to confuse people with duplicate posting.
February 16, 2011 at 8:31 am
Thanks and will do so.
August 18, 2011 at 3:07 pm
I did a quick test as following:
1) Created a test job with a simple t-sql step and scheduled the job to run every minute.
Test:
1) Enabled Job - Enabled Schedule - Job runs at schedule
2) Disabled Job - Enabled Schedule - Job DOES NOT run
Now, I tried to mess around with it by:
3) Enabled Job - Modified Schedule
I had the job monitor refresh every 3 seconds
In the agent, the snapped time for the next run for the job remains the same as per the old schedule even though the schedule was modified to run at a later time or earlier time.
After the job runs at the last snapped schedule time in the next run for the job (as per the old schedule), the next run snap time modifies as per the new schedule but not at the time when the change in schedule is made.
I guess the job schedule in the cache is refreshed for each job only after the last cached next run time for the job is past.
May sound silly but something i wanted to share with you guys.
Thanks
-
John
August 18, 2011 at 3:08 pm
I did a quick test as following:
1) Created a test job with a simple t-sql step and scheduled the job to run every minute.
Test:
1) Enabled Job - Enabled Schedule - Job runs at schedule
2) Disabled Job - Enabled Schedule - Job DOES NOT run
Now, I tried to mess around with it by:
3) Enabled Job - Modified Schedule
I had the job monitor refresh every 3 seconds
In the agent, the snapped time for the next run for the job remains the same as per the old schedule even though the schedule was modified to run at a later time or earlier time.
After the job runs at the last snapped schedule time in the next run for the job (as per the old schedule), the next run snap time modifies as per the new schedule but not at the time when the change in schedule is made.
I guess the job schedule in the cache is refreshed for each job only after the last cached next run time for the job is past.
May sound silly but something i wanted to share with you guys.
Thanks
-
John
Viewing 15 posts - 16 through 30 (of 32 total)
You must be logged in to reply to this topic. Login to reply