This will be referred to in a future post.
USE Admin
GO
IF OBJECT_ID('dbo.Job_DisableJobsThatAreEnabled') Is Not Null
DROP PROC Job_DisableJobsThatAreEnabled
GO
CREATE PROC dbo.Job_DisableJobsThatAreEnabled
@Debug bit
= 0
AS
/* DATE AUTHOR REMARKS
6/19/12 PPaiva Initial
creation.
DESCRIPTION
1. Checks for a previous backup of table
JobsBUPreviouslyEnabled.
2. Backs up sysjobs in msdb to
JobsBUPreviouslyEnabled in msdb.
3. Disables all jobs that were enabled.
4. Calls out sp_update job for each job, so that
the Agent
cache is refreshed.
NOTES
A. This is useful if you want to shut down SQL
Agent but don't
want to do it while
any jobs are executing, and if you don't
want to manually
disable all the jobs.
B. Run this proc, then wait for jobs to finish
executing, then
shut down SQL
Agent.
C. Do whatever task you have in mind.
D. When SQL Agent is running again and you are
ready to return
the jobs to their
original state, run <Job_EnableJobsThatWereEnabled>.
USAGE
-- To disable jobs, show
SQL only
Exec
Job_DisableJobsThatAreEnabled 1
-- To disable jobs,
Execute
Exec Job_DisableJobsThatAreEnabled
-- After running the
above, verify that all jobs are disabled
SELECT *
FROM vwJob
-- When ready to
re-enable the jobs, run
Exec
Job_EnableJobsThatWereEnabled 1 --
Show stats only
Exec
Job_EnableJobsThatWereEnabled -- Execute
DEBUG
Exec
dbo.DropObjectIfExists 'u', 'msdb', 'dbo', 'JobsBUPreviouslyEnabled'
SELECT *
FROM
msdb.dbo.JobsBUPreviouslyEnabled
WHERE Enabled = 1
SELECT *
FROM msdb.dbo.sysjobs
WHERE Enabled = 1
SELECT *
FROM
msdb..JobsBUPreviouslyEnabled
Exec Admin.dbo.JobStatus
*/
SET NOCOUNT ON
DECLARE
@LastBUDate datetime,
@Sql varchar(300)
IF @Debug = 0
BEGIN
IF OBJECT_ID('msdb.dbo.JobsBUPreviouslyEnabled') Is Not Null
BEGIN
Print
'Table < msdb.dbo.JobsBUPreviouslyEnabled >
already exists.
You
may first need to run < Job_EnableJobsThatWereEnabled > so as
to
restore job enabled/disabled attributes.'
RETURN
END
-- Back up
job info
SELECT
GetDate()
InsertDate, *
INTO
msdb.dbo.JobsBUPreviouslyEnabled
FROM
msdb.dbo.sysjobs
ALTER
TABLE msdb.dbo.JobsBUPreviouslyEnabled ADD
CONSTRAINT
PK_JobsBUPreviouslyEnabled PRIMARY KEY (Job_ID)
DECLARE
@JobID varchar(38),
@MaxJobID varchar(38),
@JobName sysname,
@Num smallint
SELECT @MaxJobID = Max(Convert(varchar(38), Job_ID)),
@Num = Count(*),
@JobID = ''
FROM
msdb.dbo.JobsBUPreviouslyEnabled
WHERE
Enabled = 1
-- Diable
jobs
WHILE
@JobID < @MaxJobID
BEGIN
--
Get next JobID
SELECT
@JobID = Min(Convert(varchar(38), Job_ID))
FROM
msdb.dbo.JobsBUPreviouslyEnabled
WHERE
Enabled = 1
AND Convert(varchar(38), Job_ID) > @JobID
--
Get other attributes
SELECT
@JobName = Name
FROM
msdb.dbo.JobsBUPreviouslyEnabled
WHERE Convert(varchar(38), Job_ID) = @JobID
SET
@Sql = 'Exec
msdb.dbo.sp_update_job @Job_ID = ''' +
@JobID + ''', @Enabled
= 0'
IF
@Debug = 1
Print @Sql
ELSE
Exec (@Sql)
END
Print
'-- Number of jobs that were disabled: ' + Convert(varchar, @Num)
Print
''
SELECT
Name EnabledJobName, Job_ID JobID, Enabled
FROM
msdb.dbo.JobsBUPreviouslyEnabled
WHERE
Enabled = 1
ORDER
BY Name
SELECT
Name DisabledJobName, Job_ID JobID, Enabled
FROM
msdb.dbo.JobsBUPreviouslyEnabled
WHERE
Enabled = 0
ORDER
BY Name
Print
'To re-enable these jobs, run < Exec
Admin.dbo.Job_EnableJobsThatWereEnabled >.'
END
ELSE IF @Debug = 1
BEGIN
IF Object_ID('msdb.dbo.JobsBUPreviouslyEnabled') Is Not Null
BEGIN
--
Get date of last BU
SELECT
@LastBUDate = crDate
FROM
msdb.dbo.sysobjects
WHERE
Name = 'JobsBUPreviouslyEnabled'
AND xType = 'u'
--
Check for previous Back Up
IF
@LastBUDate Is Not
Null
BEGIN
SET @Sql = 'There is already a backup from ' + Convert(varchar, @LastBUDate, 120) + '.'
SET @Sql = @Sql + Char(13) + Char(10) + 'Consider running < Exec
Job_EnableJobsThatWereEnabled > to restore all jobs to original state.'
SET @Sql = @Sql + Char(13) + Char(10) + 'Or, to permanently destroy
the last back up, make a new backup, and disable all jobs, rerun this proc as
such:'
SET @Sql = @Sql + Char(13) + Char(10) + ' Exec Job_DisableJobsThatAreEnabled 1'
RaisError (@Sql, 0, 1)
SELECT Name EnabledJobName,
Job_ID JobID, Enabled
FROM msdb.dbo.JobsBUPreviouslyEnabled
WHERE Enabled = 1
ORDER BY Name
SELECT Name DisabledJobName,
Job_ID JobID, Enabled
FROM msdb.dbo.JobsBUPreviouslyEnabled
WHERE Enabled = 0
ORDER BY Name
RETURN
END
END
ELSE
BEGIN
--
Summary of jobs
SELECT
Enabled, Count(*) QtyJobs
FROM
msdb.dbo.sysjobs
GROUP
BY Enabled
ORDER
BY 1
END
END
