Sql server > Sql Server >> Bussiness Intelligence Views : 1129
Rate This Article :

SQL JOB amendments

Job Script:

We can drop or create jobs.

Can we alter the Jobs?  --Yes we can but not using ALTER keyword.

We have different SPs to alter the job properties like schehules, steps...etc  

Some are here-

-- creates a job step that that uses Transact-SQL

USE msdb;

GO

EXEC sp_add_jobstep

@job_name = N'Weekly Sales Data Backup',

@step_name = N'Set database to read only',

@subsystem = N'TSQL',

@command = N'ALTER DATABASE SALES SET READ_ONLY',

@retry_attempts = 5,

@retry_interval = 5 ;

GO

--To drop existing schedule

USE msdb ;

GO

EXEC dbo.sp_detach_schedule

    @job_name = 'START BATCH - DSAS',

    @schedule_name = 'START BATCH - DSAS' ;

GO

 

--To create new schedule

DECLARE @jobId BINARY(16)

DECLARE @ReturnCode INT

SELECT @ReturnCode = 0

set @jobId=(SELECT JOB_ID FROM msdb.dbo.sysjobs where name like '%START BATCH - DSAS%')

EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1

EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'START BATCH - DSAS',

            @enabled=1,

            @freq_type=8,

            @freq_interval=127,

            @freq_subday_type=1,

            @freq_subday_interval=0,

            @freq_relative_interval=0,

            @freq_recurrence_factor=1,

            @active_start_date=20120417,

            @active_end_date=99991231,

            @active_start_time=10000,

            @active_end_time=235959,

            @schedule_uid=N'e3633b86-f1a9-401e-9459-c53e6302764b'

 

-->Each Job will have a unique id (check job ids: select job_id from msdb.dbo.sysjobs) likewise each shedule have a uniqueid. If we try to use existing schedule Id for another new schedule then both schedule times effects for the new schedule job.

--> why we are not going for drop and create of a job instead altering the job properties?

Ans: there are several reasons for this query. You can have a consistent job id every time, even job properties changes also.

Other SPs used here are:

sp_add_category

sp_add_job

sp_update_job

sp_delete_job

sp_help_job-->Returns information about jobs that are used by SQL Server Agent to perform automated activities in SQL Server. 

sp_update_jobstep -> Changes the setting for a step in a job that is used to perform automated activities.

sp_start_job--> Instructs SQL Server Agent to execute a job immediately.

sp_stop_job-->Instructs SQL Server Agent to stop the execution of a job.

SP_GET_COMPOSITE_JOB_INFO-->to identify the currently running jobs.

Disable All SQL Server Agent Jobs:

USE MSDB;
GO
UPDATE MSDB.dbo.sysjobs
SET Enabled = 0
WHERE Enabled = 1;
GO

Enable All SQL Server Agent Jobs:

USE MSDB;
GO
UPDATE MSDB.dbo.sysjobs
SET Enabled = 1
WHERE Enabled = 0;
GO

Disable Jobs By Job Name:

USE MSDB;
GO
UPDATE MSDB.dbo.sysjobs
SET Enabled = 0
WHERE [Name] LIKE 'Admin%';
GO

Disable Jobs By Job Category:

USE MSDB;
GO
UPDATE J
SET J.Enabled = 0
FROM MSDB.dbo.sysjobs J
INNER JOIN MSDB.dbo.syscategories C
ON J.category_id = C.category_id
WHERE C.[Name] = 'Database Maintenance';
GO

---------------------------------

 

About Author
Prathap Reddy Golledla
Total Posts 2
Comment this article
Name*
Email Address* (Will not be shown on this website.)
Comments*
Enter Image Text*
   
View All Comments
swarup
good one
pbi
super point excellent
super article
excellent point
super article
excellent point
  Privacy   Terms Of Use   Advertise With Us   Contact Us
© 2016 Developerin.Net. All rights reserved.
Trademarks and Article Images mentioned in this site may belongs to Microsoft and other respective trademark owners.
Articles, Tutorials and all other content offered here is for educational purpose only and its author copyrights.