Michael L John - Saturday, September 22, 2018 4:40 PM
Hi John,
I tried something different and the sql procedure which is mentioned below will fetch all the sql jobs getting executed at different servers (condition is that all the servers names must be stored in server_list table).
As per my requirement I also appended stop job proc into the same procedure.
If not required then comment out those codes.
--SQL PROCEDURE START
ALTER PROCEDURE [dbo].[get_all_servers_job_executing_list]
AS
BEGIN
SET NOCOUNT ON;
declare @servercount varchar(max);
declare @servername varchar(max);
select @servercount = count(*) from server_list --(server_list is the table where all the server names are stored. It must have a column ---server_name) ;
declare @jobname varchar(max)
declare @query varchar (max);
declare @query1 varchar (1000);
declare @query2 varchar (1000);
declare @i varchar(max) = 1;
declare @JOBcount varchar(max);
declare @j-2 varchar(max) = 1;
create TABLE records1
(
[Job ID] UNIQUEIDENTIFIER,[Last Run Date] CHAR(8),[Last Run Time] CHAR(6),[Next Run Date] CHAR(8),[Next Run Time] CHAR(6),[Next Run Schedule ID] INT,
[Requested To Run] INT,[Request Source] INT,[Request Source ID] SQL_VARIANT,[Running] INT,[Current Step] INT,[Current Retry Attempt] INT,[State] INT
)
while (@i <= @servercount )
begin
WITH s AS (
SELECT (ROW_NUMBER() OVER (ORDER BY server_name)) as row,server_name
FROM server_list )
SELECT @servername= server_name FROM s WHERE row = @i
set @query1= 'INSERT INTO records1 EXECUTE ' + @servername +' .master.dbo.xp_sqlagent_enum_jobs @can_see_all_running_jobs=1,@job_owner=''0x4A6F6E47757267756C'''
--print (@query1)
exec (@query1);
alter table records1 add server_name varchar(100)
update records1 set server_name = @servername
set @query = 'SElECT
t.server_name,
t.[Request Source ID] [Requester]
,t.[Job ID] [JobID]
,sj.[name] [JobName]
,sjs.[step_id] [StepID]
,sjs.[step_name] [StepName]
into records2
FROM records1 t
INNER JOIN '+@servername+'. msdb.dbo.sysjobs sj ON t.[Job ID] = sj.[job_id]
INNER JOIN '+@servername+'. msdb.dbo.sysjobsteps sjs ON sjs.[job_id] = sj.[job_id]
AND t.[Job ID] = sjs.[job_id]
AND t.[Current Step] = sjs.[step_id]
INNER JOIN
(
SELECT * FROM '+@servername+' . msdb.dbo.sysjobactivity d
WHERE EXISTS
(
SELECT 1
FROM ' +@servername+' . msdb.dbo.sysjobactivity l
GROUP BY l.[job_id]
HAVING l.[job_id] = d.[job_id]
AND MAX(l.[start_execution_date]) = d.[start_execution_date]
)
) sja
ON sja.[job_id] = sj.[job_id]
LEFT JOIN (SELECT SUBSTRING([program_name],30,34) p,[session_id] FROM ' +@servername+' .msdb . sys.dm_exec_sessions
WHERE [program_name] LIKE ''SQLAgent - TSQL JobStep%'') es
ON CAST('''' AS XML).value(''xs:hexBinary(substring(sql:column("es.p"),3))'',''VARBINARY(MAX)'') = sj.[job_id]'
--print (@query)
exec (@query)
alter table records1 drop column server_name;
SELECT * INTO RECORDS3 FROM records2
DROP TABLE mi_uwhca.dbo. records2
select @JOBcount = count(*) from RECORDS3 ;
while (@J <= @JOBcount )
begin
WITH P AS (
SELECT (ROW_NUMBER() OVER (ORDER BY JOBname)) as row,JOBname
FROM RECORDS3 )
SELECT @JOBname= JOBname FROM P WHERE row = @j-2
set @query2 = 'exec ' +@servername +'. msdb.[dbo].[sp_stop_job]' + @JOBNAME
--print(@query2)
exec (@query2)
PRINT @JOBNAME + ' STOPPED EXEXCUTING'
SET @j-2 = @j-2+1
END
insert into records4 select * from records3 --where jobname in (select distinct jobname from RECORDS3 )
drop table RECORDS3
set @i= @i+1
truncate table records1
end
drop table records1
END
--SQL PROCEDURE END
Results will be saved in table records4.
Please let me know if any further query arises.
Thank You,
Prakash Anand