December 1, 2011 at 1:56 pm
just a little but crucial warning;
- double check your jobs aren't launched using sqlalert(s) ! If a jobs can be launched by a trigger and you disable the job, when the alert gets raised, sqlagent will try to start the job about 6 times a second until it succeeds. It records every attempt in sqlagent.out .... until it runs out of disk space !!!
- Make sure you leave a trail who/why a job has been disabled. The goal of this is to not re-enable jobs that haven't been disabled by your proc and for others so they know why jobs have been disabled and who to contact!
- also keep in mind when someone uses sp_start_job, that doesn't take the enabled flag into account, so it just gets launched !!
FYI I added the sprocs I use to disable and enable jobs.
edited .... please excuse me for the Dutch comments in the scripts.:Whistling:
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
December 2, 2011 at 6:16 am
Guras (12/1/2011)
GSquared (12/1/2011)
Of course, the other option would be to put a first step in those jobs that would check the time/date, and cause the job to abort if run during the desired window.That way, you don't have to worry about "did I remember to turn those jobs back on?" and that kind of thing.
Far too easy to be on vacation or something, and the person covering for you doesn't know to turn them off and back on. Or turns them off, gets distracted, forgets to turn them back on. Or to get distracted yourself and forget one or the other.
But a first step that checks "am I supposed to run now?" and aborts the job covers that whole problem nicely.
Simple enough to have a step generate an error if run during the "don't run now" window, and to set that step to "continue to next step" on success, and "quit job reporting success" on error. Or variations thereof.
Another advantage to that is that it's semi-self-documenting. A replacement or vacation coverage person can open up the job itself and see a step in it that has that behavior, and has a description in the step of what it does and why.
I see what you are saying GSquared and that is a briallant idea. This route lets me have a peaceful vacation as well w/o having to worry if the jobs are turned off/ON....I will try to put that in a script and add as the first step in each job that needs to be turned off EOM. However I have a question on this. Let's say I check the current time and date and if it is 11:30 PM EOM then abort the job with script
exec msdb..sp_update_job @job_name = 'Update_job1, @enabled = 0
So the Step2 ( exec myupdateProc) will not fire right? I would think so since the job is disabled in Step 1 but just confirming.
THANKS!
I think you'd have to issue a stop command rather than disabling it. Disabling it mid-run might do unpredictable things.
Also, disabling it, even if it works, means you'll still have to re-enable it manually. It doesn't allow for automatic re-enabling, if that's what you want.
If you want to go that route, set up a separate job, scheduled to run shortly before this one, that disables this one, and then another that re-enables this one after the right events are done.
The problem with that is, again, you have to count on other people not looking at the disabled job and going, "Why is that disabled?" and re-enabling it when it's not supposed to run. That's the primary advantage to a run-check in the first step.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
December 2, 2011 at 8:29 am
GSquared (12/2/2011)
Guras (12/1/2011)
GSquared (12/1/2011)
Of course, the other option would be to put a first step in those jobs that would check the time/date, and cause the job to abort if run during the desired window.That way, you don't have to worry about "did I remember to turn those jobs back on?" and that kind of thing.
Far too easy to be on vacation or something, and the person covering for you doesn't know to turn them off and back on. Or turns them off, gets distracted, forgets to turn them back on. Or to get distracted yourself and forget one or the other.
But a first step that checks "am I supposed to run now?" and aborts the job covers that whole problem nicely.
Simple enough to have a step generate an error if run during the "don't run now" window, and to set that step to "continue to next step" on success, and "quit job reporting success" on error. Or variations thereof.
Another advantage to that is that it's semi-self-documenting. A replacement or vacation coverage person can open up the job itself and see a step in it that has that behavior, and has a description in the step of what it does and why.
I see what you are saying GSquared and that is a briallant idea. This route lets me have a peaceful vacation as well w/o having to worry if the jobs are turned off/ON....I will try to put that in a script and add as the first step in each job that needs to be turned off EOM. However I have a question on this. Let's say I check the current time and date and if it is 11:30 PM EOM then abort the job with script
exec msdb..sp_update_job @job_name = 'Update_job1, @enabled = 0
So the Step2 ( exec myupdateProc) will not fire right? I would think so since the job is disabled in Step 1 but just confirming.
THANKS!
I think you'd have to issue a stop command rather than disabling it. Disabling it mid-run might do unpredictable things.
Also, disabling it, even if it works, means you'll still have to re-enable it manually. It doesn't allow for automatic re-enabling, if that's what you want.
If you want to go that route, set up a separate job, scheduled to run shortly before this one, that disables this one, and then another that re-enables this one after the right events are done.
The problem with that is, again, you have to count on other people not looking at the disabled job and going, "Why is that disabled?" and re-enabling it when it's not supposed to run. That's the primary advantage to a run-check in the first step.
If I try to stop the job say at 11:29 PM ( even when the job is not running) it will give out the error, The request to stop job refused because the job is not currently running. Then it might run at 11:30 PM. This is the reason I want to disable the job.
December 2, 2011 at 8:36 am
Guras (12/2/2011)
GSquared (12/2/2011)
Guras (12/1/2011)
GSquared (12/1/2011)
Of course, the other option would be to put a first step in those jobs that would check the time/date, and cause the job to abort if run during the desired window.That way, you don't have to worry about "did I remember to turn those jobs back on?" and that kind of thing.
Far too easy to be on vacation or something, and the person covering for you doesn't know to turn them off and back on. Or turns them off, gets distracted, forgets to turn them back on. Or to get distracted yourself and forget one or the other.
But a first step that checks "am I supposed to run now?" and aborts the job covers that whole problem nicely.
Simple enough to have a step generate an error if run during the "don't run now" window, and to set that step to "continue to next step" on success, and "quit job reporting success" on error. Or variations thereof.
Another advantage to that is that it's semi-self-documenting. A replacement or vacation coverage person can open up the job itself and see a step in it that has that behavior, and has a description in the step of what it does and why.
I see what you are saying GSquared and that is a briallant idea. This route lets me have a peaceful vacation as well w/o having to worry if the jobs are turned off/ON....I will try to put that in a script and add as the first step in each job that needs to be turned off EOM. However I have a question on this. Let's say I check the current time and date and if it is 11:30 PM EOM then abort the job with script
exec msdb..sp_update_job @job_name = 'Update_job1, @enabled = 0
So the Step2 ( exec myupdateProc) will not fire right? I would think so since the job is disabled in Step 1 but just confirming.
THANKS!
I think you'd have to issue a stop command rather than disabling it. Disabling it mid-run might do unpredictable things.
Also, disabling it, even if it works, means you'll still have to re-enable it manually. It doesn't allow for automatic re-enabling, if that's what you want.
If you want to go that route, set up a separate job, scheduled to run shortly before this one, that disables this one, and then another that re-enables this one after the right events are done.
The problem with that is, again, you have to count on other people not looking at the disabled job and going, "Why is that disabled?" and re-enabling it when it's not supposed to run. That's the primary advantage to a run-check in the first step.
If I try to stop the job say at 11:29 PM ( even when the job is not running) it will give out the error, The request to stop job refused because the job is not currently running. Then it might run at 11:30 PM. This is the reason I want to disable the job.
I thought you were talking about putting the command as the first step in the job. If that's the case, it will definitely be running when the stop command is issued.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
December 2, 2011 at 8:46 am
GSquared (12/2/2011)
Guras (12/2/2011)
GSquared (12/2/2011)
Guras (12/1/2011)
GSquared (12/1/2011)
Of course, the other option would be to put a first step in those jobs that would check the time/date, and cause the job to abort if run during the desired window.That way, you don't have to worry about "did I remember to turn those jobs back on?" and that kind of thing.
Far too easy to be on vacation or something, and the person covering for you doesn't know to turn them off and back on. Or turns them off, gets distracted, forgets to turn them back on. Or to get distracted yourself and forget one or the other.
But a first step that checks "am I supposed to run now?" and aborts the job covers that whole problem nicely.
Simple enough to have a step generate an error if run during the "don't run now" window, and to set that step to "continue to next step" on success, and "quit job reporting success" on error. Or variations thereof.
Another advantage to that is that it's semi-self-documenting. A replacement or vacation coverage person can open up the job itself and see a step in it that has that behavior, and has a description in the step of what it does and why.
I see what you are saying GSquared and that is a briallant idea. This route lets me have a peaceful vacation as well w/o having to worry if the jobs are turned off/ON....I will try to put that in a script and add as the first step in each job that needs to be turned off EOM. However I have a question on this. Let's say I check the current time and date and if it is 11:30 PM EOM then abort the job with script
exec msdb..sp_update_job @job_name = 'Update_job1, @enabled = 0
So the Step2 ( exec myupdateProc) will not fire right? I would think so since the job is disabled in Step 1 but just confirming.
THANKS!
I think you'd have to issue a stop command rather than disabling it. Disabling it mid-run might do unpredictable things.
Also, disabling it, even if it works, means you'll still have to re-enable it manually. It doesn't allow for automatic re-enabling, if that's what you want.
If you want to go that route, set up a separate job, scheduled to run shortly before this one, that disables this one, and then another that re-enables this one after the right events are done.
The problem with that is, again, you have to count on other people not looking at the disabled job and going, "Why is that disabled?" and re-enabling it when it's not supposed to run. That's the primary advantage to a run-check in the first step.
If I try to stop the job say at 11:29 PM ( even when the job is not running) it will give out the error, The request to stop job refused because the job is not currently running. Then it might run at 11:30 PM. This is the reason I want to disable the job.
I thought you were talking about putting the command as the first step in the job. If that's the case, it will definitely be running when the stop command is issued.
ok, I see what you are saying. I like the idea of stoping the job vs disabling it.
December 2, 2011 at 9:43 am
I tried something like this but now reaching a road block
Declare @stopTime as varchar(7),@SystemNowTime as varchar(7),@EOMdate varchar(10)
SET @EOMdate = convert(varchar,DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+1,0)),101)
SET @stopTime = '11:30PM'
SET @SystemNowTime = RIGHT('0' + RIGHT(GETDATE(),7),7)
SELECT @stopTime,@SystemNowTime,@EOMdate
If (CONVERT(varchar, getdate(), 101) = @EOMdate AND @SystemNowTime >= @stopTime)
BEGIN
--I need to find the current status of the job and then stop it if it is running.
--how do I find the status of the current job ???END
December 13, 2011 at 6:13 am
(Just got back from a vacation, hence delay in answering.)
You can use sp_help_job to get that, but why do you need it? If the script is being run by the job, the job is definitely running.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
December 13, 2011 at 6:35 am
Perhaps set up another job that runs at or just before the cut off time, which determines current activity & stops where necessary then disables?
You could use sp_help_jobactivity,
or a query like use msdb
go
select j.name, ja.start_execution_date
from dbo.sysjobs j
inner join dbo.sysjobactivity ja on ja.job_id = j.job_id
where ja.start_execution_date is not null
and ja.stop_execution_date is null
to see what's running, and then sp_stop_job where necessary to stop the ones you want.
December 17, 2013 at 8:14 am
Try this.
USE MSDB;
GO
UPDATE MSDB.dbo.sysjobs
SET Enabled = 0
WHERE Enabled = 1;
GO
Ishtiaq Ahmed
Viewing 9 posts - 16 through 23 (of 23 total)
You must be logged in to reply to this topic. Login to reply