E9.2 Scheduler question

[ascampbell]

Would anyone mind sharing how you are handling notifications when the scheduler stops. I would like to setup a notification (if possible) to get some type of warning that the scheduler kernel has stopped. I've looked thru server manager and the logic server the scheduler runs on but I guess I'm blind.

 

[Larry_Jones]

Server Manager can alert if a process goes zombie

 

[glen7x]

An idea would be to run a test job (a simple, harmless UBE) as frequent as you would like and monitor job completion. There are tools available to check monitor the runs and alert you if there are failures.

 

[brother_of_karamazov]

Would anyone mind sharing how you are handling notifications when the scheduler stops. I would like to setup a notification (if possible) to get some type of warning that the scheduler kernel has stopped. I've looked thru server manager and the logic server the scheduler runs on but I guess I'm blind.

Step 1. Create F986110_History.

CREATE TABLE [SVM920].[F986110_History](
[JCJOBQUE] [nchar](10) NULL,
[JCJOBPTY] [nchar](2) NULL,
[JCJOBSTS] [nchar](2) NULL,
[JCENHV] [nchar](10) NULL,
[JCUSER] [nchar](10) NULL,
[JCPSWD] [nchar](10) NULL,
[JCJOBTYPE] [nchar](2) NULL,
[JCSBMDATE] [numeric](18, 0) NULL,
[JCSBMTIME] [float] NULL,
[JCJOBNBR] [float] NOT NULL,
[JCEXEHOST] [nchar](40) NOT NULL,
[JCORGHOST] [nchar](40) NULL,
[JCPROCESSID] [float] NULL,
[JCACTDATE] [numeric](18, 0) NULL,
[JCACTTIME] [float] NULL,
[JCFUNO] [nchar](4) NULL,
[JCPRTQ] [nchar](10) NULL,
[JCFNDFUF1] [nchar](30) NULL,
[JCFNDFUF2] [nchar](30) NULL,
[modified_date] [datetime] NULL,
[action] [varchar](15) NULL
) ON [PRIMARY]

GO

Step 2. Create triggers.

CREATE TRIGGER [trgInsertHistory] ON [SVM920].[F986110]
FOR INSERT
AS
SET NOCOUNT ON
Insert svm920.f986110_History (JCJOBQUE, JCJOBPTY, JCJOBSTS, JCENHV, JCUSER, JCPSWD, JCJOBTYPE, JCSBMDATE, JCSBMTIME, JCJOBNBR, JCEXEHOST, JCORGHOST, JCPROCESSID, JCACTDATE, JCACTTIME, JCFUNO, JCPRTQ, JCFNDFUF1, JCFNDFUF2, modified_date, Action)
select JCJOBQUE, JCJOBPTY, JCJOBSTS, JCENHV, JCUSER, JCPSWD, JCJOBTYPE, JCSBMDATE, JCSBMTIME, JCJOBNBR, JCEXEHOST, JCORGHOST, JCPROCESSID, JCACTDATE, JCACTTIME, JCFUNO, JCPRTQ, JCFNDFUF1, JCFNDFUF2, GETDATE() , 'INSERTED'
from inserted

CREATE TRIGGER [trgUpdateHistory] ON [SVM920].[F986110]
FOR UPDATE
AS
SET NOCOUNT ON
Insert svm920.f986110_History (JCJOBQUE, JCJOBPTY, JCJOBSTS, JCENHV, JCUSER, JCPSWD, JCJOBTYPE, JCSBMDATE, JCSBMTIME, JCJOBNBR, JCEXEHOST, JCORGHOST, JCPROCESSID, JCACTDATE, JCACTTIME, JCFUNO, JCPRTQ, JCFNDFUF1, JCFNDFUF2, modified_date, Action)
select JCJOBQUE, JCJOBPTY, JCJOBSTS, JCENHV, JCUSER, JCPSWD, JCJOBTYPE, JCSBMDATE, JCSBMTIME, JCJOBNBR, JCEXEHOST, JCORGHOST, JCPROCESSID, JCACTDATE, JCACTTIME, JCFUNO, JCPRTQ, JCFNDFUF1, JCFNDFUF2, GETDATE() , 'UPDATED'
from inserted
where JCJOBSTS in ('D','E','P')

CREATE TRIGGER [trgDeleteHistory] ON [SVM920].[F986110]
FOR DELETE
AS
SET NOCOUNT ON
Insert svm920.f986110_History (JCJOBQUE, JCJOBPTY, JCJOBSTS, JCENHV, JCUSER, JCPSWD, JCJOBTYPE, JCSBMDATE, JCSBMTIME, JCJOBNBR, JCEXEHOST, JCORGHOST, JCPROCESSID, JCACTDATE, JCACTTIME, JCFUNO, JCPRTQ, JCFNDFUF1, JCFNDFUF2, modified_date, Action)
select JCJOBQUE, JCJOBPTY, JCJOBSTS, JCENHV, JCUSER, JCPSWD, JCJOBTYPE, JCSBMDATE, JCSBMTIME, JCJOBNBR, JCEXEHOST, JCORGHOST, JCPROCESSID, JCACTDATE, JCACTTIME, JCFUNO, JCPRTQ, JCFNDFUF1, JCFNDFUF2, GETDATE() , 'DELETED'
from deleted
where JCUSER != 'SCHEDULER'


===

Create triggers for each Server Map

Step 3. Setup a scheduler job that runs R0006P every fifteen minutes.

Step 4. Create SQL Agent job that checks every 15 minutes, five minutes after the scheduled test job is run to see if it ran and emails if it didn't.

IF (SELECT DATEDIFF (MINUTE, (SELECT TOP 1 JDE920.SVM920.F986110_History.modified_date FROM JDE920.SVM920.F986110_History

WHERE JCJOBQUE = 'SCHD920'

and JCFNDFUF2 like 'R0006P_XJDE0005%'

and JCJOBSTS = 'D'

ORDER by JDE920.SVM920.F986110_History.modified_date DESC),GETDATE())

) > 15

EXEC msdb.dbo.sp_send_dbmail

@profile_name = 'default',

@recipients ='[email protected]',

@subject = 'Customername Scheduler Alert',

@body = 'Customername scheduler may be down.



', @query = 'SET NOCOUNT ON

SELECT GETDATE() as ''Current Date/Time''

PRINT ''''

SELECT TOP 10 JDE920.SVM920.F986110_History.modified_date as ''Run Date'', JCFNDFUF2 as ''Job'' FROM JDE920.SVM920.F986110_History

WHERE JCJOBQUE = ''SCHD920''

and JCFNDFUF2 like ''R0006P_XJDE0005%''

and JCJOBSTS = ''D''

ORDER by JDE920.SVM920.F986110_History.modified_date DESC

PRINT ''''

SELECT LTRIM(DATEDIFF (MINUTE, (SELECT TOP 1 JDE920.SVM920.F986110_History.modified_date FROM JDE920.SVM920.F986110_History

WHERE JCJOBQUE = ''SCHD920''

and JCFNDFUF2 like ''R0006P_XJDE0005%''

and JCJOBSTS = ''D''

ORDER by JDE920.SVM920.F986110_History.modified_date DESC),GETDATE())) as ''Time Difference (Mins)''

SET NOCOUNT ON'

Step 5. Setup mail forwarding to send a text message for Scheduler Down alerts.


You'll receive emails that look like this:

Customername scheduler may be down.


Current Date/Time
-----------------------
2021-08-29 06:25:00.977

Run Date Job
----------------------- ------------------------------
2021-08-29 06:07:01.623 R0006P_XJDE0005
2021-08-29 05:50:03.310 R0006P_XJDE0005
2021-08-29 05:35:03.070 R0006P_XJDE0005
2021-08-29 05:20:03.310 R0006P_XJDE0005
2021-08-29 05:05:02.517 R0006P_XJDE0005
2021-08-29 04:50:01.673 R0006P_XJDE0005
2021-08-29 04:35:01.700 R0006P_XJDE0005
2021-08-29 04:20:01.147 R0006P_XJDE0005
2021-08-29 04:05:05.993 R0006P_XJDE0005
2021-08-29 03:50:05.943 R0006P_XJDE0005

Time Difference (Mins)
----------------------
18

F986110_History is also useful for analyzing UBE run times, etc.

 

[brother_of_karamazov]

It looks like I also figured out a way to do it without using F986110_History but you get UTC time from F986114:

IF (SELECT DATEDIFF (MINUTE, (SELECT TOP 1 JDE900.SVM900C.F986114.JCETDTIM
FROM JDE900.SVM900C.F986114
WHERE JCPID = 'R0006P'
and JCVERS = 'XJDE0005'
and JCJOBSTS = 'D'
ORDER by JDE900.SVM900C.F986114.JCETDTIM DESC),GETUTCDATE())
) > 15

EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'default',
@recipients = '[email protected]',
@subject = 'Customername Scheduler Alert',
@body = 'Customername scheduler may be down.

', @query = 'SET NOCOUNT ON
SELECT GETUTCDATE() as ''Current UTC Date/Time''
PRINT ''''
SELECT GETDATE() as ''Current Date/ Time''
PRINT ''''
SELECT TOP 10 JDE900.SVM900C.F986114.JCETDTIM as ''UTC Run Date'', JCPID as ''Job'' FROM JDE900.SVM900C.F986114
WHERE JCPID = ''R0006P''
and JCJOBSTS = ''D''
ORDER by JDE900.SVM900C.F986114.JCETDTIM DESC
PRINT ''''
SELECT DATEDIFF (MINUTE, (SELECT TOP 1 JDE900.SVM900C.F986114.JCETDTIM
FROM JDE900.SVM900C.F986114
WHERE JCPID = ''R0006P''
and JCVERS = ''XJDE0005''
and JCJOBSTS = ''D''
ORDER by JDE900.SVM900C.F986114.JCETDTIM DESC),GETUTCDATE()) as ''Time Difference (Mins)''
SET NOCOUNT ON'