SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Disable all the SQL jobs at once


Disable all the SQL jobs at once

Author
Message
Guras
Guras
SSCertifiable
SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)

Group: General Forum Members
Points: 5689 Visits: 1360
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
Richard Moore-400646
Richard Moore-400646
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2148 Visits: 1640
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.
HowardW
HowardW
One Orange Chip
One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)

Group: General Forum Members
Points: 27179 Visits: 9893
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
GSquared
GSquared
SSC Guru
SSC Guru (241K reputation)SSC Guru (241K reputation)SSC Guru (241K reputation)SSC Guru (241K reputation)SSC Guru (241K reputation)SSC Guru (241K reputation)SSC Guru (241K reputation)SSC Guru (241K reputation)

Group: General Forum Members
Points: 241568 Visits: 9733
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
Dev
Dev
SSC-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39550 Visits: 1604
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.
Craig Wilkinson
Craig Wilkinson
SSC-Dedicated
SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)

Group: General Forum Members
Points: 38350 Visits: 8627
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.


Forever trying to learn
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

If you litter your database queries with nolock query hints, are you aware of the side effects? Try reading a few of these links...
(*) Missing rows with nolock
(*) Allocation order scans with nolock(*) Consistency issues with nolock
(*) Transient Corruption Errors in SQL Server error log caused by nolock
(*) Dirty reads, read errors, reading rows twice and missing rows with nolock

Craig Wilkinson - Software Engineer
LinkedIn
Guras
Guras
SSCertifiable
SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)

Group: General Forum Members
Points: 5689 Visits: 1360
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
GSquared
GSquared
SSC Guru
SSC Guru (241K reputation)SSC Guru (241K reputation)SSC Guru (241K reputation)SSC Guru (241K reputation)SSC Guru (241K reputation)SSC Guru (241K reputation)SSC Guru (241K reputation)SSC Guru (241K reputation)

Group: General Forum Members
Points: 241568 Visits: 9733
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
GSquared
GSquared
SSC Guru
SSC Guru (241K reputation)SSC Guru (241K reputation)SSC Guru (241K reputation)SSC Guru (241K reputation)SSC Guru (241K reputation)SSC Guru (241K reputation)SSC Guru (241K reputation)SSC Guru (241K reputation)

Group: General Forum Members
Points: 241568 Visits: 9733
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
Dev
Dev
SSC-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39550 Visits: 1604
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum









































































































































































SQLServerCentral


Search