Monday, September 28, 2015

SQL Script to Disable or Delete all SQL Agent Jobs that are active

In order to execute the extended stored procedure (XP) to see all SQL Agent and their status, you must have permissions to Execute Agent XPs

use master
go
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Agent XPs', 1;
go
RECONFIGURE
GO


Here is the script to create each line for execution:

declare @sql nvarchar(max) 
DECLARE @isDelete AS BIT
DECLARE @job_owner AS sysname
DECLARE @xp_results TABLE (job_id                UNIQUEIDENTIFIER NOT NULL,
                            last_run_date         INT              NOT NULL,
                            last_run_time         INT              NOT NULL,
                            next_run_date         INT              NOT NULL,
                            next_run_time         INT              NOT NULL,
                            next_run_schedule_id  INT              NOT NULL,
                            requested_to_run      INT              NOT NULL, -- BOOL
                            request_source        INT              NOT NULL,
                            request_source_id     sysname          COLLATE database_default NULL,
                            running               INT              NOT NULL, -- BOOL
                            current_step          INT              NOT NULL,
                            current_retry_attempt INT              NOT NULL,
                            job_state             INT              NOT NULL)

SET @job_owner = SUSER_SNAME()
set @sql = ''
/* If @isDelete is set to 1, it will create a script to delete all jobs that are disabled. */
SET @isDelete = 0

/* 1. Get list of Jobs and state*/
INSERT INTO @xp_results
EXECUTE master.dbo.xp_sqlagent_enum_jobs @can_see_all_running_jobs=1,  @job_owner = @job_owner

SELECT * FROM [@xp_results]

/* 2. Disable all jobs, only need to disable those that are running */
select
@sql = @sql + N'exec msdb.dbo.sp_update_job @job_name = ''' + name + N''', @enabled = 0;
' from msdb.dbo.sysjobs sj JOIN @xp_results r ON sj.job_id = r.job_id
where enabled = 1
order by name

print @sql
exec (@sql)

/* 3. Stop all running jobs */
/*Stop Job*/

PRINT 'Stoping Jobs...'
SET @sql = ''

select
@sql = @sql + N'exec msdb.dbo.sp_stop_job @job_name = ''' + name + N''';
' from msdb.dbo.sysjobs sj JOIN @xp_results r ON sj.job_id = r.job_id
where enabled = 0 AND r.job_state = 1
order by name

print @sql
exec (@sql)

/*Delete Job*/
IF @isDelete = 1
BEGIN
select
@sql = @sql + N'exec msdb.dbo.sp_delete_job @job_name = ''' + name + N''';
' from msdb.dbo.sysjobs
where enabled = 0
order by name

print @sql
exec (@sql)
END

Once Complete Select and copy all rows.
Paste into new script window and execute.   Be aware that executing the delete will delete all disabled jobs.





No comments:

Post a Comment