I have found in many of the blogs where it has been
advised to update the system table in msdb database to enable/disable the SQL
job(s). This does not seem good to me. Microsoft recommends do not try to
update the system table directly.
Here is the simple script that I have written to
disable/enable all the SQL jobs at one time.
Disable the Jobs:
DECLARE
@sql_command VARCHAR(MAX)
SET
@sql_command=''SELECT @sql_command=@sql_command+' EXEC msdb..sp_update_job @job_id='''+cast(job_id AS VARCHAR(100))+''',@enabled=0;' FROM msdb..sysjobs
WHERE enabled=1
EXEC(@sql_command)
Enable the Jobs:
DECLARE
@sql_command VARCHAR(MAX)
SET
@sql_command=''SELECT @sql_command=@sql_command+' EXEC msdb..sp_update_job @job_id='''+cast(job_id AS VARCHAR(100))+''',@enabled=1;' FROM msdb..sysjobs
WHERE enabled=0
EXEC(@sql_command)
NOTE: Remember if you have to disable all the SQL jobs in
a server, you can stop SQL server agent job as well. This will not allow any
schedule jobs to run automatically. If this is not possible then you can use
above script. If you have to disable some of the jobs you can expand the where
clause. You can also use the LIKE keyword filter some of the jobs. Up to you to
modify the script and get the required action done.
Hope this helps.
No comments:
Post a Comment