job control script

-- --------------------------------------------------------------------------------------------------------------
-- job disable enable automation script
-- Use RAISERROR instead of print.
-- --------------------------------------------------------------------------------------------------------------
select @@servername 

use dba 
go

if object_id('AgentJobDisable') is not null
begin 
	drop table AgentJobDisable
end 

create table AgentJobDisable
(
 idx								int identity(1,1)
,workId								int
,job_id								varchar(100)
,name								nvarchar(1000)
,backup_run_requested_date			datetime
,backup_start_execution_date		datetime
,backup_job_history_id				int
,backup_next_scheduled_run_date		datetime 
,insTime							datetime
,disableStartTime					datetime
,disableStopTime					datetime
,isManualStartedYN					varchar(1)
,isManualStartedTime				datetime
,isEnabledYN						varchar(1)
,isEnabledTime						datetime
)
go

declare @workId int = 6
select * from dba.dbo.AgentJobDisable where workId = @workId 


-- --------------------------------------------------------------------------------------------------------------
-- step 1 start
-- WorkId and time must be set.
-- Change workId at once with ctrl - h -> @workId int = 6
-- Get the disable task list.
-- Puts only the task list that needs to run at the specified time into the list.
-- --------------------------------------------------------------------------------------------------------------

use dba 
go
select @@servername 
declare @workId int = 6
declare @disableStartTime datetime = '2023-04-14 12:00:00.000'
declare @disableStopTime datetime  = '2023-04-14 22:50:00.000'
declare @insTime datetime = getdate() 
declare @message nvarchar(3000) = N''

begin try 
	if exists (select * from dba.dbo.AgentJobDisable where workId = @workId)
	begin 
		set @message = @@servername + 'workId ' + cast(@workId as varchar(100)) +' exist.' ;
		throw 51000, @message, 1;  
	end 

	insert into AgentJobDisable
		(
		 workId							
		,job_id							
		,name							
		,backup_run_requested_date		
		,backup_start_execution_date
		,backup_job_history_id			
		,backup_next_scheduled_run_date	
		,insTime						
		,disableStartTime				
		,disableStopTime				
		)
	select @workId, a.job_id, a.name, b.run_requested_date, b.start_execution_date, b.job_history_id, b.next_scheduled_run_date, @insTime, @disableStartTime, @disableStopTime
	from msdb.dbo.sysjobs a 
		cross apply (
			select top 1 * 
			from msdb.dbo.sysjobactivity
			where job_id = a.job_id
			and next_scheduled_run_date >= @disableStartTime
			and next_scheduled_run_date <= @disableStopTime
			order by next_scheduled_run_date desc 
		) b
	where a.enabled = 1
		and name not in ('[hourly]Transaction Log Backup', 'syspolicy_purge_history')
	order by next_scheduled_run_date desc 
end try
begin catch 
	throw; 
end catch 

select @workId workId 
go

-- --------------------------------------------------------------------------------------------------------------
-- step 1 end 
-- --------------------------------------------------------------------------------------------------------------

-- disable job target list 
select @@servername 
declare @workId int = 6
select * from dba.dbo.AgentJobDisable where workId = @workId
go

-- --------------------------------------------------------------------------------------------------------------
-- step 2 start 
-- disable job 
-- Disable all tasks found in the list.
-- --------------------------------------------------------------------------------------------------------------

select @@servername 
declare @workId int = 6
declare @minIdx int 
declare @jobName nvarchar(2000) 
declare @cmd nvarchar(max)
declare @message nvarchar(3000)

begin try 
	select @minIdx = min (idx) from dba.dbo.AgentJobDisable where workId = @workId
	while (1=1)
	begin 
		select @jobName = name from dba.dbo.AgentJobDisable where idx = @minIdx 
		if @@rowcount > 0 
		begin 
			set @message = @@servername + ' idx : ' + cast(@minIdx as varchar(max)) + ' workId : ' + cast(@workId as varchar(max)) + ' jobName : ' + @jobName + ' jobDisableStart'
			RAISERROR(@message, 1,1 ) WITH LOG, NOWAIT

			set @cmd = 'EXEC msdb.dbo.sp_update_job @job_name = N'''+@jobName+ ''', @enabled = 0'
			
			set @message = @cmd
			RAISERROR(@message, 1,1 ) WITH LOG, NOWAIT

			exec (@cmd) 

			update dba.dbo.AgentJobDisable 
				set isEnabledYN = 'N'
				, isManualStartedYN ='N' 
			where idx = @minIdx

			set @message = @@servername + ' idx : ' + cast(@minIdx as varchar(max)) + ' workId : ' + cast(@workId as varchar(max)) + ' jobName : ' + @jobName + ' jobDisableCompleted' 
			RAISERROR(@message, 1,1 ) WITH LOG, NOWAIT
		end 
		else 
		begin 
			break; 
		end 
		select top 1 @minIdx = idx from dba.dbo.AgentJobDisable where workId = @workId and idx > @minIdx order by idx asc
		if @@rowcount = 0 
			break; 

		set @message = '*****************************************************************' 
		RAISERROR(@message, 1,1 ) WITH LOG, NOWAIT
	end 
end try 
begin catch 
	throw; 
end catch 
go

-- --------------------------------------------------------------------------------------------------------------
-- step 2 end 
-- --------------------------------------------------------------------------------------------------------------

-- just check 
select @@servername 
declare @workId int = 6
select * from dba.dbo.AgentJobDisable where workId = @workId
go
select * from msdb.dbo.sysjobs where enabled = 0

-- --------------------------------------------------------------------------------------------------------------
-- step 3 start 
-- After checking
-- Returns the job that needs to be run.
-- If the checking is finished early, only enable do not run.
-- If the script that was supposed to run didn't run, run and enable it.
-- --------------------------------------------------------------------------------------------------------------

select @@servername 
declare @workId int = 6
declare @minIdx int 
declare @jobName nvarchar(2000) 
declare @job_id nvarchar(100)
declare @cmd nvarchar(max)
declare @backup_next_scheduled_run_date datetime 
declare @disableStartTime datetime
declare @run_requested_date datetime
declare @message nvarchar(3000)

begin try 
	select @minIdx = min (idx) 
	from dba.dbo.AgentJobDisable 
	where workId = @workId

	while (1=1)
	begin 

		set @jobName = ''
		set @job_id = ''

		select top 1 @jobName = name
			, @job_id = job_id
			, @disableStartTime = disableStartTime
		from dba.dbo.AgentJobDisable 
		where idx = @minIdx 
			and isManualStartedYN = 'N'
			and isEnabledYN = 'N'

		if @@rowcount > 0 
		begin 
			select top 1 @backup_next_scheduled_run_date = backup_next_scheduled_run_date from dba.dbo.AgentJobDisable where idx = @minIdx and job_id = @job_id
			if (@backup_next_scheduled_run_date >= @disableStartTime and @backup_next_scheduled_run_date < getdate()) 
			begin 
				if (@jobName = '[daily]cafe_Stat') -- manual job
				begin 
					set @message = 'skipped job : [daily]cafe_Stat'
					RAISERROR(@message, 1,1 ) WITH LOG, NOWAIT
				end 
				else 
				begin 
					set @message = @@servername + ' jobName : ' + @jobName +', ** RUN ** commnet : isManualStartedYN N,  backup_next_scheduled_run_date : ' + convert(varchar(max), @backup_next_scheduled_run_date, 121) + ' current time : ' +convert(varchar(max), getdate(), 121)
					RAISERROR(@message, 1,1 ) WITH LOG, NOWAIT

					set @cmd = 'EXEC msdb.dbo.sp_start_job @job_name = N'''+@jobName+ ''''
					set @message = @cmd
					RAISERROR(@message, 1,1 ) WITH LOG, NOWAIT

					exec (@cmd)

					update dba.dbo.AgentJobDisable set isManualStartedYN = 'Y', isManualStartedTime =getdate() where idx = @minIdx

					set @message = @@servername + ' jobName : ' + @jobName +', commnet : sp_start_job executed'
					RAISERROR(@message, 1,1 ) WITH LOG, NOWAIT
				end 				
			end
			else -- does not match time
			begin 
				set @message = @@servername + ' jobName : ' + @jobName +', ** SKIP **, commnet : isManualStartedYN N,  backup_next_scheduled_run_date : ' + convert(varchar(max), @backup_next_scheduled_run_date, 121) + ' current time : ' +convert(varchar(max), getdate(), 121)
				RAISERROR(@message, 1,1 ) WITH LOG, NOWAIT
			end 

			-- common job enable 
			set @message = @@servername + ' idx : ' + cast(@minIdx as varchar(max)) + ' workId : ' + cast(@workId as varchar(max)) + ' jobName : ' + @jobName + ' jobEnableStart'
			RAISERROR(@message, 1,1 ) WITH LOG, NOWAIT

			set @cmd = 'EXEC msdb.dbo.sp_update_job @job_name = N'''+@jobName+ ''', @enabled = 1'
			set @message = @cmd
			RAISERROR(@message, 1,1 ) WITH LOG, NOWAIT

			exec (@cmd) 
			update dba.dbo.AgentJobDisable set isEnabledYN = 'Y', isEnabledTime = getdate() where idx = @minIdx

			set @message = @@servername + ' idx : ' + cast(@minIdx as varchar(max)) + ' workId : ' + cast(@workId as varchar(max)) + ' jobName : ' + @jobName + ' jobEnableCompleted' 
			RAISERROR(@message, 1,1 ) WITH LOG, NOWAIT
			
			set @message = '*****************************************************************' 
			RAISERROR(@message, 1,1 ) WITH LOG, NOWAIT
		end 
		else -- no more job
		begin 
			break; 
		end 
		select top 1 @minIdx = idx from dba.dbo.AgentJobDisable where workId = @workId and idx > @minIdx order by idx asc
		if @@rowcount = 0 
			break; 
	end 
end try 
begin catch 
	throw; 
end catch 
go

-- --------------------------------------------------------------------------------------------------------------
-- step 3 end 
-- --------------------------------------------------------------------------------------------------------------

-- just check 
select @@servername 
declare @workId int = 6
select * from dba.dbo.AgentJobDisable where workId = @workId
go

sp_readerrorlog 
select * from msdb.dbo.sysjobs where enabled = 0

-- --------------------------------------------------------------------------------------------------------------
-- job enable script start
-- --------------------------------------------------------------------------------------------------------------
select @@servername 
declare @workId int = 6
select * from dba.dbo.AgentJobDisable where workId = @workId
go

select @@servername 
declare @workId int = 6
declare @minIdx int 
declare @jobName nvarchar(2000) 
declare @job_id nvarchar(100)
declare @cmd nvarchar(max)
declare @next_scheduled_run_date datetime 
declare @disableStartTime datetime
declare @run_requested_date datetime
declare @message nvarchar(3000)

begin try 
	update dba.dbo.AgentJobDisable 
	set isEnabledYN = 'N' 
	where workId = @workId 

	select @minIdx = min (idx) 
	from dba.dbo.AgentJobDisable 
	where workId = @workId

	while (1=1)
	begin 

		set @jobName = ''
		set @job_id = ''


		select top 1 @jobName = name
			, @job_id = job_id
			, @disableStartTime = disableStartTime
		from dba.dbo.AgentJobDisable 
		where idx = @minIdx 
			and isEnabledYN = 'N'

		if @@rowcount > 0 
		begin 
			-- common job enable (항상 활성화 시킨다.)
			set @message = @@servername + ' idx : ' + cast(@minIdx as varchar(max)) + ' workId : ' + cast(@workId as varchar(max)) + ' jobName : ' + @jobName + ' jobEnableStart'
			RAISERROR(@message, 1,1 ) WITH LOG, NOWAIT

			set @cmd = 'EXEC msdb.dbo.sp_update_job @job_name = N'''+@jobName+ ''', @enabled = 1'

			set @message = @cmd
			RAISERROR(@message, 1,1 ) WITH LOG, NOWAIT

			exec (@cmd) 
			update dba.dbo.AgentJobDisable set isEnabledYN = 'Y', isEnabledTime = getdate() where idx = @minIdx
			
			set @message = @@servername + ' idx : ' + cast(@minIdx as varchar(max)) + ' workId : ' + cast(@workId as varchar(max)) + ' jobName : ' + @jobName + ' jobEnableCompleted' 
			RAISERROR(@message, 1,1 ) WITH LOG, NOWAIT

		end 
		else 
		begin 
			break; 
		end 
		select top 1 @minIdx = idx from dba.dbo.AgentJobDisable where workId = @workId and idx > @minIdx order by idx asc
		if @@rowcount = 0 
			break; 
	end 
end try 
begin catch 
	throw; 
end catch 
go

-- -------------------------------------------
-- job enable script end
-- -------------------------------------------