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.
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
Paste into new script window and execute. Be aware that executing the delete will delete all disabled jobs.
No comments:
Post a Comment