create table #jobruns(
	job_id uniqueidentifier not null,
	originating_server nvarchar(30) not null,
	name sysname not null,
	startjob datetime not null,
	endjob as
		( dateadd(hour,cast(left(runduration_hhhhmmss,4) as int),
			dateadd(minute,cast(substring(runduration_hhhhmmss,5,2) as int),
				dateadd(second,cast(right(runduration_hhhhmmss,2) as int),startjob) ) ) ),
	runduration_hhhhmmss char(8),
	run_status int not null,
	status as(
		case sjh.run_status
			when 0 then 'Failed'
			when 1 then 'Succeeded'
			when 2 then 'Retry'
			when 3 then 'Canceled'
			when 4 then 'In progress'
			else ltrim(sjh.run_status) end ),
	constraint pk_#jobruns_ primary key ( job_id, startjob ),
	constraint uc_#jobruns_ unique clustered( startjob, originating_server, name ) )

insert #jobruns(
	job_id,
	originating_server,
	name,
	startjob,
	runduration_hhhhmmss,
	run_status )
select
	sj.job_id,
	sj.originating_server,
	sj.name,
	startjob = cast(ltrim(sjh.run_date)
	+' '+left(substring('000000',1,6-len(sjh.run_time))+ltrim(sjh.run_time),2)
	+':'+substring(substring('000000',1,6-len(sjh.run_time))+ltrim(sjh.run_time),3,2)
	+':'+right(substring('000000',1,6-len(sjh.run_time))+ltrim(sjh.run_time),2) as datetime),
	runduration_hhhhmmss = 
	left(substring('00000000',1,8-len(sjh.run_duration))+ltrim(sjh.run_duration),4)
	+substring(substring('00000000',1,8-len(sjh.run_duration))+ltrim(sjh.run_duration),5,2)
	+right(substring('00000000',1,8-len(sjh.run_duration))+ltrim(sjh.run_duration),2),
	sjh.run_status
from
	msdb.dbo.sysjobs sj
	join msdb.dbo.sysjobhistory sjh on sj.job_id = sjh.job_id
where
	sjh.step_id = 0

select * from #jobruns

drop table #jobruns

/*

CREATE TABLE [dbo].[monitor_jobs_on_these_servers] (
	[servername] [sysname] NOT NULL ,
	[counter] [int] IDENTITY (1, 1) NOT NULL 
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[monitor_jobs_on_these_servers] WITH NOCHECK ADD 
	CONSTRAINT [pk_monitor_jobs_on_these_servers] PRIMARY KEY  CLUSTERED 
	(
		[counter]
	)  ON [PRIMARY] 
GO

 CREATE  UNIQUE  INDEX [IX_monitor_jobs_on_these_servers] ON [dbo].[monitor_jobs_on_these_servers]([servername]) ON [PRIMARY]
GO


create table jobmon_history
(
job_name sysname,
run_status varchar(15),
message nvarchar(1024),
run_date datetime,
run_duration smallint,
server sysname
)

create unique clustered index clx1 on jobmon_history (server, job_name, run_date ) with ignore_dup_key
*/

alter PROC jobmon
@servername nvarchar(30) = NULL,
@history bit = 0
AS
set nocount on 
DECLARE @counter smallint

/* Call this with a servername to see jobs for one server, or with no parameter to
see all the servers in the table monitor_jobs_on_these_servers. The proc also
updates the jobmon_history table with any new job failures. 

If called with @history = 1 the proc will simply show the existing history table, without checking the latest
job situation. You can also view the history for just one server.

*/

--declare @servername nvarchar (30)
--SELECT @servername =  servername from monitor_jobs_on_these_servers

--select @servername

If @history = 1 begin

SELECT * from jobmon_history
WHERE @servername is null or @servername = server

END ELSE BEGIN

	IF @servername is null BEGIN
	--print 'hello'
	select @counter = 0
	WHILE @counter < (SELECT max (counter) from monitor_jobs_on_these_servers)
	BEGIN
	SELECT TOP 1 @counter =  counter, @servername = servername from monitor_jobs_on_these_servers where counter > @counter
	--print @counter
	--print @servername
	exec jobmon @servername
	END
	
	END ELSE BEGIN
	
	declare @sql nvarchar (1400)
	
	select @sql =
	'SELECT  
		job_name = sj.name,
	        case sjh.run_status 
			when 0 then ''Failed ''
			when 1 then ''Succeeded ''
			when 2 then ''Retry (step only) ''
			when 3 then ''Canceled ''
			when 4 then ''In-progress message ''
			when 5 then ''Unknown ''
		end as [run status ],
		message,
		convert (datetime, convert(varchar(9),run_date),112) + convert (datetime,left ((convert (varchar(9), run_time)),2)+'':''+right(left ((convert (varchar(9), run_time)),4),2)+'':''+right(((convert (varchar(9), run_time))),2),8) as run_date,
	        sjh.run_duration,
	        sjh.server
	FROM 
		[' + @servername + '].msdb.dbo.sysjobhistory                sjh 
	INNER JOIN 
		[' + @servername + '].msdb.dbo.sysjobs_view                 sj
		on sjh.job_id = sj.job_id
	where
	run_status in (0,3,4,5) -- Failed, cancelled, in prog, unknown
	and
	step_id > 0 -- this seems to give more useful info
	
	group by name, run_status, message, run_date, run_duration, server, run_time
	order by run_date
	'
	
	exec sp_executesql  @statement = @sql
	
	select @sql = 'INSERT INTO jobmon_history ' + @sql
	
	exec sp_executesql  @statement = @sql
	END
END

create table #xp_results(
	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 )

insert #xp_results exec master.dbo.xp_sqlagent_enum_jobs @is_sysadmin = 1, @job_owner = ''

select job_id as currently_running_jobs
from #xp_results
where running = 1

drop table #xp_results

--USAGE : jobmon 'servername' - Running jobs & cancelled/failed jobs for one server
--jobmon -- Running jobs & cancelled/failed jobs for all servers named in supporting table
--jobmon @history = 1 - Shows old cancelled jobs from the history.
--jobmon 'servername',@history = 1 - shows history for one server.


/*

CREATE TABLE [dbo].[monitor_jobs_on_these_servers] (
	[servername] [sysname] NOT NULL ,
	[counter] [int] IDENTITY (1, 1) NOT NULL 
) ON [PRIMARY]


ALTER TABLE [dbo].[monitor_jobs_on_these_servers] WITH NOCHECK ADD 
	CONSTRAINT [pk_monitor_jobs_on_these_servers] PRIMARY KEY  CLUSTERED 
	(
		[counter]
	)  ON [PRIMARY] 


 CREATE  UNIQUE  INDEX [IX_monitor_jobs_on_these_servers] ON [dbo].[monitor_jobs_on_these_servers]([servername]) ON [PRIMARY]



create table jobmon_history
(
job_name sysname,
run_status varchar(15),
message nvarchar(1024),
run_date datetime,
run_duration smallint,
server sysname
)

create unique clustered index clx1 on jobmon_history (server, job_name, run_date ) with ignore_dup_key
*/

alter PROC jobmon
@servername nvarchar(30) = NULL,
@history bit = 0
AS
set nocount on 
DECLARE @counter smallint

/* 

This procedure will show you failed jobs since the last time that the procedure
was ran, and any currently running jobs (without date/time.)

Call this with a servername to see failed jobs for one server, or with no parameter to
see all the servers in the table monitor_jobs_on_these_servers. The proc also
updates the jobmon_history table with any failures. 

If called with @history = 1 the proc will simply show the existing history table, without checking the latest
job situation. You can also view the history for just one server.

*/

--declare @servername nvarchar (30)
--SELECT @servername =  servername from monitor_jobs_on_these_servers

--select @servername

If @history = 1 begin

SELECT * from jobmon_history
WHERE @servername is null or @servername = server
order by server, job_name, run_date

END ELSE BEGIN

	IF @servername is null BEGIN
	--print 'hello'
	select @counter = 0
	WHILE @counter < (SELECT max (counter) from monitor_jobs_on_these_servers)
	BEGIN
	SELECT TOP 1 @counter =  counter, @servername = servername from monitor_jobs_on_these_servers where counter > @counter
	--print @counter
	--print @servername
	exec jobmon @servername
	END
	
	END ELSE BEGIN
	
	declare @sql nvarchar (3500)
	declare @selonly nvarchar (2200)
	
	select @sql =
	'SELECT  
		job_name = sj.name,
	        case sjh.run_status 
			when 0 then ''Failed ''
			when 1 then ''Succeeded ''
			when 2 then ''Retry (step only) ''
			when 3 then ''Canceled ''
			when 4 then ''In-progress message ''
			when 5 then ''Unknown ''
		end as [run status ],
		sjh.message,
		convert (datetime, convert(varchar(9),sjh.run_date),112) + convert (datetime,left ((right (''0'' + convert (varchar(9), sjh.run_time),6)),2)+'':''+right(left ((right (''0'' + convert (varchar(9), sjh.run_time),6)),4),2)+'':''+right(((right (''0'' + convert (varchar(9), sjh.run_time),6))),2),8) as run_date,
	        sjh.run_duration,
	        sjh.server
	FROM 
		[' + @servername + '].msdb.dbo.sysjobhistory                sjh 
	INNER JOIN 
		[' + @servername + '].msdb.dbo.sysjobs_view                 sj
		on sjh.job_id = sj.job_id
	LEFT OUTER JOIN 
		jobmon_history jh
		on jh.job_name = sj.name AND jh.server = sjh.server and jh.run_date = convert (datetime, convert(varchar(9),sjh.run_date),112) + convert (datetime,left ((right (''0'' + convert (varchar(9), sjh.run_time),6)),2)+'':''+right(left ((right (''0'' + convert (varchar(9), sjh.run_time),6)),4),2)+'':''+right(((right (''0'' + convert (varchar(9), sjh.run_time),6))),2),8)
		and 
		(
		sjh.run_status = 0 and jh.[run_status] =  ''Failed '' OR
		sjh.run_status = 1 and jh.[run_status] =  ''Succeeded '' OR
		sjh.run_status = 2 and jh.[run_status] =  ''Retry (step only) '' OR
		sjh.run_status = 3 and jh.[run_status] =  ''Canceled '' OR
		sjh.run_status = 4 and jh.[run_status] =  ''In-progress message '' OR
		sjh.run_status = 5 and jh.[run_status] =  ''Unknown ''
		)
	where
	sjh.run_status in (0,3,4,5) -- Failed, cancelled, in prog, unknown
	and
	step_id > 0 -- this seems to give more useful info
	and
	jh.job_name is null
	
	group by name, sjh.run_status, sjh.message, sjh.run_date, sjh.run_duration, sjh.server, run_time
	order by sjh.server,sjh.run_date
	'
	
	select @selonly = @sql

	select @sql = 
	'
	create table  #xp_results (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)

	INSERT into #xp_results exec (''['+ @servername + '].master.dbo.xp_sqlagent_enum_jobs 1,'''''''''')


	select s.name, ''Running'' as run_status, ''Executing Job Step '' + convert(varchar(4),step_id) + '' : '' + step_name as ''Message'',
	Null as run_date,
	NULL as ''run_duration'', originating_server as server
	from #xp_results x 
	inner join msdb..sysjobs s on x.job_id = s.job_id 
	inner join msdb..sysjobsteps ss on ss.job_id = s.job_id and ss.step_id = current_step
	where running = 1
	
	union all
	' + @sql + '
	DROP TABLE #xp_results'

--	print @sql

	exec sp_executesql  @statement = @sql
	
	select @sql = 'INSERT INTO jobmon_history ' + @selonly
	
	exec sp_executesql  @statement = @sql
	END
END

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

ALTER  PROCEDURE EMCBackupLogRestoresDisable

--Name:		EMCBackupLogRestoresDisable
--
-- (c) 2004 NovaStar Mortgage, Inc.
--
--Purpose: 	This stored Procedure(SP) stops log restores and verifies the restores have stopped.
--
--Format:	EXEC EMCBackupLogRestoresDisable @emc_clone_master_key
--
--Example:	EXEC EMCBackupLogRestoresDisable '1'
--
--Action:	Author:			Date:		Comments:
---------	-------------   --/--/----	----------------------------------------
--Created	Derrick Leggett	06/10/2004 	Initial Development 
--Modified	Derrick Leggett	06/25/2004	Added nocount.
--Modified	Derrick Leggett	07/19/2004	Added error handling.
--

@emc_clone_master_key INT

AS

SET NOCOUNT ON

--Declare needed variables.
DECLARE
	@sql NVARCHAR(2500),
	@emc_clone_details_procedure VARCHAR(256),
	@emc_clone_details_type_key VARCHAR(55),
	@emc_clone_details_info VARCHAR(1000),
	@emc_clone_details_parameters VARCHAR(2000),
	@cmd NVARCHAR(2500),
	@string NVARCHAR(4000),
	@job_disabled_successfully INT,
	@loop INT, --Current iteration of loop.
	@loop_cycle INT, --Variable to record how max times to loop.
	@loop_lag CHAR(8) --Variable to record how long between loops.

SELECT 
	@emc_clone_details_procedure = (SELECT OBJECT_NAME(@@PROCID)),
	@emc_clone_details_info = '',
	@emc_clone_details_parameters = '@emc_clone_master_key = ' + CAST(@emc_clone_master_key AS VARCHAR(55)),
	@loop = 1, 
	@loop_cycle = 240,
	@loop_lag = '00:00:30',
	@job_disabled_successfully = 0

--Record the start of process.
EXEC EMCCloneDetailsPut
	@emc_clone_master_key, 
	@emc_clone_details_procedure, 
	'Started', 
	@emc_clone_details_info,
	@emc_clone_details_parameters

--Drop/Create temp table to hold status.
IF (SELECT OBJECT_ID('tempdb..#status')) IS NOT NULL
	BEGIN
		DROP TABLE #status
	END

CREATE TABLE #status(
	job_id UNIQUEIDENTIFIER,
	originating_server NVARCHAR(256),
	name NVARCHAR(256),
	enabled INT,
	description VARCHAR(2000),
	start_step_id INT,
	category VARCHAR(55),
	owner VARCHAR(55),
	notify_level_eventlog INT,
	notify_level_email INT,
	notify_level_netsend INT,
	notify_level_page INT,
	notify_email_operator VARCHAR(55),
	notify_netsend_operator VARCHAR(55),
	notify_page_operator VARCHAR(55),
	delete_level INT,
	date_created DATETIME,
	date_modified DATETIME,
	version_number INT,
	last_run_date CHAR(8),
	last_run_time VARCHAR(8),
	last_run_outcome INT,
	next_run_date CHAR(8),
	next_run_time VARCHAR(8),
	next_run_schedule_id INT,
	current_execution_status INT,
	current_execution_step VARCHAR(255),
	current_retry_attempt INT,
	has_step INT,
	has_schedule INT,
	has_target INT,
	type INT)

--Turn the restore job off.
EXEC MKCSSQL04.msdb.dbo.sp_update_job @job_name = 'Restore Transaction Logs from Prod', @enabled = '0'

IF @@ERROR <> 0
	BEGIN
		SELECT @emc_clone_details_info = @emc_clone_details_info + 'Failure on sp_update_job.'
		GOTO ERROR_HANDLE
	END

--Verify the job has been disabled.
IF (SELECT enabled FROM MKCSSQL04.msdb.dbo.sysjobs WHERE name = 'Restore Transaction Logs from Prod' AND enabled = '0') IS NULL
	BEGIN
		SELECT @emc_clone_details_info = @emc_clone_details_info + 'Job was not disabled.'
		GOTO ERROR_HANDLE
	END

--Loop through until the restore job shows as being disabled.  
--  When it's been disabled, report success.
--  If it takes more then 30 minutes, report a failure.

WHILE @loop <= @loop_cycle
	BEGIN

		DELETE #status
		INSERT #status
		EXEC MKCSSQL04.msdb.dbo.sp_get_composite_job_info @execution_status = 1
		
		IF @@ERROR <> 0
			BEGIN
				SELECT @emc_clone_details_info = @emc_clone_details_info + 'Failure on sp_get_composite_job_info.'
				GOTO ERROR_HANDLE
			END

		IF (SELECT TOP 1 job_id FROM #status) IS NULL
			BEGIN
				SELECT @job_disabled_successfully = 1
				GOTO END_LOOP
			END

		WAITFOR DELAY @loop_lag
	
	END

END_LOOP:

IF @job_disabled_successfully = 1
	BEGIN

		--Record the success of process.
		EXEC EMCCloneDetailsPut
			@emc_clone_master_key, 
			@emc_clone_details_procedure, 
			'Success', 
			@emc_clone_details_info,
			@emc_clone_details_parameters

		RETURN 0
	END
ELSE
	BEGIN
		SELECT @emc_clone_details_info = @emc_clone_details_info + 'Job disabled, but has not completed.'
		GOTO ERROR_HANDLE

	END

ERROR_HANDLE:

	--Record the failure of process.
	EXEC EMCCloneDetailsPut
		@emc_clone_master_key, 
		@emc_clone_details_procedure, 
		'Failure', 
		@emc_clone_details_info,
		@emc_clone_details_parameters

	--Raiserror and exit batch with failure.
	RAISERROR(@emc_clone_details_info,16,1)
	RETURN -1

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO