December 1, 2011 at 7:45 am
We have several sql jobs ( about 10 jobs) that need to be disabled at 11:30 PM at the end of the month, each month in SQL server 2008. Is there a way to disable all the jobs at once. Currently I go to each job and I set the end date schedule as the last day of the month at 11:30 PM.
I was hoping there should be a better way than this.
Thanks for the help
December 1, 2011 at 7:52 am
Go to the Job Activity Monitor and ctrl-left click on each, then right click and select disable job, or you can write a script against msdb.dbo.sysjobs and update the enabled column.
December 1, 2011 at 8:02 am
Easy enough, Just use something like this to generate the statements:
--generate disable
SELECT 'exec msdb..sp_update_job @job_name = '''+NAME+''', @enabled = 0' FROM msdb..sysjobs
--generate enable
SELECT 'exec msdb..sp_update_job @job_name = '''+NAME+''', @enabled = 1' FROM msdb..sysjobs
Then schedule SQL jobs from the results at the time you need
December 1, 2011 at 8:04 am
Any reason not to just turn off the SQL Agent service during that window? That'll turn off all jobs in one command. You can turn that off and on via command line, just like any other service, and can schedule the off/on commands via the Windows task scheduler.
- 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 1, 2011 at 8:07 am
GSquared (12/1/2011)
Any reason not to just turn off the SQL Agent service during that window? That'll turn off all jobs in one command. You can turn that off and on via command line, just like any other service, and can schedule the off/on commands via the Windows task scheduler.
It will disable casual / on request tasks as well say Export / Import.
December 1, 2011 at 9:01 am
DECLARE @sql AS VARCHAR(MAX)
SELECT @sql = COALESCE(@SQL+ + CHAR(13), '') + 'EXEC msdb..sp_update_job @job_name = ''' + name + ''', @enabled = 0;' FROM msdb.dbo.sysjobs
EXEC (@SQL)
I'd do what G-Squared suggested though.
December 1, 2011 at 9:09 am
I took the route of what HowardW sugested since I realized there were some notifications jobs that needed to be running.
Here is my sp
CREATE PROCEDURE [dbo].[PROC_SQLJOBS_ENABLE_DISABLE] (@enableFlag int)
AS
/** ***** Version *****
* $Revision:$
* $Date: $
* $Author $
* $Archive:$
*
* Sample use: exec PROC_SQLJOBS_ENABLE_DISABLE 0
* Comments : This stored procedure disables or enables all the listed jobs in the sp at once
* @enableFlag = 0 Disable jobs
* @enableFlag = 1 Enable jobs
*/
If @enableFlag = 0 --disable jobs
BEGIN
exec msdb..sp_update_job @job_name = 'Update_job1, @enabled = 0
exec msdb..sp_update_job @job_name = 'Update_job2', @enabled = 0
END
If @enableFlag = 1 --enable jobs
BEGIN
exec msdb..sp_update_job @job_name = 'Update_job1, @enabled = 1
exec msdb..sp_update_job @job_name = 'Update_job2', @enabled = 1
END
GO
Now taking this to the next level I want to capture error msg from each step if any and send out email to the admin
Any help would be greatly appreciated.
Thanks
December 1, 2011 at 9:16 am
Dev (12/1/2011)
GSquared (12/1/2011)
Any reason not to just turn off the SQL Agent service during that window? That'll turn off all jobs in one command. You can turn that off and on via command line, just like any other service, and can schedule the off/on commands via the Windows task scheduler.It will disable casual / on request tasks as well say Export / Import.
Nope. Just tested it (SQL 2008 R2 Dev Edition). Turned off SQL Agent, then used the Export wizard to export a table to a text file. Worked as expected.
Some things, like the Copy Database Wizard, depend on SQL Agent jobs, but not usual Export/Import wizards.
- 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 1, 2011 at 9:17 am
Guras (12/1/2011)
I took the route of what HowardW sugested since I realized there were some notifications jobs that needed to be running.Here is my sp
CREATE PROCEDURE [dbo].[PROC_SQLJOBS_ENABLE_DISABLE] (@enableFlag int)
AS
/** ***** Version *****
* $Revision:$
* $Date: $
* $Author $
* $Archive:$
*
* Sample use: exec PROC_SQLJOBS_ENABLE_DISABLE 0
* Comments : This stored procedure disables or enables all the listed jobs in the sp at once
* @enableFlag = 0 Disable jobs
* @enableFlag = 1 Enable jobs
*/
If @enableFlag = 0 --disable jobs
BEGIN
exec msdb..sp_update_job @job_name = 'Update_job1, @enabled = 0
exec msdb..sp_update_job @job_name = 'Update_job2', @enabled = 0
END
If @enableFlag = 1 --enable jobs
BEGIN
exec msdb..sp_update_job @job_name = 'Update_job1, @enabled = 1
exec msdb..sp_update_job @job_name = 'Update_job2', @enabled = 1
END
GO
Now taking this to the next level I want to capture error msg from each step if any and send out email to the admin
Any help would be greatly appreciated.
Thanks
Try...Catch and sp_send_dbmail are pretty much built for that. Are you familiar with those?
- 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 1, 2011 at 9:28 am
GSquared (12/1/2011)
Dev (12/1/2011)
GSquared (12/1/2011)
Any reason not to just turn off the SQL Agent service during that window? That'll turn off all jobs in one command. You can turn that off and on via command line, just like any other service, and can schedule the off/on commands via the Windows task scheduler.It will disable casual / on request tasks as well say Export / Import.
Nope. Just tested it (SQL 2008 R2 Dev Edition). Turned off SQL Agent, then used the Export wizard to export a table to a text file. Worked as expected.
Some things, like the Copy Database Wizard, depend on SQL Agent jobs, but not usual Export/Import wizards.
I was assuming (in fact I was very confident) that Copy Database & Export / Import Wizard share same logic (common classes inside) and same requirements. It should fail if you try to export / import to other RDBMS or SQL Server.
For Text File export I believe it uses BCP internally so it doesn’t require SQL Agent.
December 1, 2011 at 9:35 am
I did something like this
Declare @errMsg varchar(1000)
If @enableFlag = 0 --disable jobs
BEGIN
BEGIN TRY
exec msdb..sp_update_job @job_name = 'Update_job1', @enabled = 0
END TRY
BEGIN CATCH
SET @errMsg = 'There was an error! ' + ERROR_MESSAGE()
END CATCH
BEGIN TRY
exec msdb..sp_update_job @job_name = 'Update_job2', @enabled = 0
END TRY
BEGIN CATCH
SET @errMsg = 'There was an error! ' + ERROR_MESSAGE()
END CATCH
If @errMsg <> ''
Begin
declare @rc int
exec @rc = msdb.dbo.sp_send_dbmail
@profile_name = 'SQLMail@thecompany.com',
@recipients = 'dataExchange@thecompany.com',
@subject = 'Job Failure - to enable/Disable jobs',
@body = @errMsg,
@body_format ='HTML'
select @rc
End
END
December 1, 2011 at 10:14 am
I like what Guras has done. You never know what jobs will change, and you might not want new jobs disabled. Or maybe you do, but if you want specific items disabled during a time period, I'd script a disable for those, and then document this in a checklist somewhere.
December 1, 2011 at 11:22 am
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.
- 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 1, 2011 at 11:46 am
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!
December 1, 2011 at 12:27 pm
The idea from GSquared is nice, but it could be a problem as well if you don't document this and make sure alerts will not fire or someone will not open tickets if these jobs do not run.
If it's only once a month, that's probably OK, but if you have more complex logic for more dates, you could easily create some level of chaos.
That being said, I'd implement Gus' solution and use it as a template for future jobs that need to be disabled.
Viewing 15 posts - 1 through 15 (of 23 total)
You must be logged in to reply to this topic. Login to reply