PSFINPRD_check_process - To check if any process is processing or in initiated status for over 60 minutes
Text
-------------------
-- exec PSFINPRD_check_process 60
-- Deep Pandey 6/11/2013
CREATE PROCEDURE PSFINPRD_check_process
@timediff int
AS
DECLARE @find1 int;
DECLARE @prcsid VARCHAR(50);
DECLARE @tableHTML NVARCHAR(MAX);
DECLARE Process_list CURSOR FOR SELECT PRCSINSTANCE FROM PSPRCSRQST
WHERE RUNSTATUS IN (6, 7)
AND DATEDIFF(MINUTE,BEGINDTTM, GETDATE()) > @timediff ;
IF OBJECT_ID('tempdb..#TempTable_PRCSList','u') IS NOT NULL
BEGIN
DROP TABLE #TempTable_PRCSList;
END
ELSE
BEGIN
CREATE TABLE #TempTable_PRCSList
(PRCSINSTANCE1 varchar(50),JOBINSTANCE1 varchar(50), PRCSTYPE1 varchar(50),
PRCSNAME1 varchar(50), SERVERNAMERUN1 varchar(50),Time_in_Minutes varchar(50), RUNSTATUS1 varchar(50));
END
OPEN Process_list;
FETCH NEXT FROM Process_list INTO @prcsid;
WHILE @@FETCH_STATUS = 0
BEGIN
SET @find1 = (SELECT DATEDIFF(MINUTE,BEGINDTTM, GETDATE())
FROM PSPRCSRQST
WHERE RUNSTATUS IN (6, 7)
AND DATEDIFF(MINUTE,BEGINDTTM, GETDATE()) > @timediff
AND PRCSINSTANCE =@prcsid)
INSERT INTO #TempTable_PRCSList(PRCSINSTANCE1, JOBINSTANCE1,PRCSTYPE1,PRCSNAME1,SERVERNAMERUN1,Time_in_Minutes, RUNSTATUS1 )
SELECT PRCSINSTANCE, JOBINSTANCE, PRCSTYPE, PRCSNAME,SERVERNAMERUN,
DATEDIFF(MINUTE,BEGINDTTM, GETDATE()) Time_in_Minutes,
CASE WHEN
RUNSTATUS = 7 THEN 'Processing'
WHEN RUNSTATUS = 6 THEN 'Initiated'
END AS RUNSTATUS
FROM PSPRCSRQST
WHERE RUNSTATUS IN (6, 7)
AND PRCSINSTANCE =@prcsid
AND DATEDIFF(MINUTE,BEGINDTTM, GETDATE()) > @timediff
FETCH NEXT FROM Process_list INTO @prcsid;
END;
SET @tableHTML =
N'<h1>Please check Finance scheduler for following process(s) running over 60 minutes now</h1>'
+ N'<table border="1">'
+ CAST ( ( (
SELECT + 'Servername: ', td = @@SERVERNAME
for xml path('tr'), TYPE)
) AS NVARCHAR(MAX) )
+ CAST ( ( (
SELECT + 'Database Name: ', td = DB_NAME()
for xml path('tr'), TYPE)
) AS NVARCHAR(MAX) )
+ CAST ( ( (
SELECT 'td/@bgcolor' = '#FF3366', td =
PRCSINSTANCE1 + ' process Instance executing ' + ' ', + PRCSNAME1+ ' ' + PRCSTYPE1 + ' ' + ' on' + ' ' +
SERVERNAMERUN1 + '' + ' server from last ' + '' + Time_in_Minutes + '' +' minutes' , ''+ ' is in ' + '' + RUNSTATUS1 +'' + ' status'
FROM #TempTable_PRCSList
for xml path('tr'), TYPE)
) AS NVARCHAR(MAX) ) +
N'</table>' +
N'<br/>'
+ N'<tr><th><br> </br></th></tr>'
+ N'<tr><th><br> </br></th></tr>'
+ N'<tr><th><br>Thanks!</br></th></tr>'
+ N'<tr><th><br>BusApps Admin.</br></th></tr>'
+ N'<br/>' ;
if (@find1 > @timediff)
BEGIN
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'NSAXBPFNDB01P',
@recipients = 'you@me.com',
@importance = high,
@body = @tableHTML,
@body_format = 'HTML',
@subject = 'Process(s) running longer than 60 minutes' ;
END
CLOSE Process_list;
DEALLOCATE Process_list;
Text - Check is process scheduler is down.
----------------------
-- exec PSFINPRD_checkschedulerstatus 1
-- Deep Pandey 6/11/2013
CREATE PROCEDURE PSFINPRD_checkschedulerstatus
@timediff int
AS
DECLARE @find1 int;
DECLARE @name VARCHAR(50);
DECLARE @tableHTML NVARCHAR(MAX);
DECLARE sCHEDULER_LIST CURSOR FOR SELECT SERVERNAME FROM PSSERVERSTAT
WHERE DATEDIFF(MINUTE,LASTUPDDTTM, GETDATE()) > @timediff;
IF OBJECT_ID('tempdb..#TempTable_PRCS','u') IS NOT NULL
BEGIN
DROP TABLE #TempTable_PRCS;
END
ELSE
BEGIN
CREATE TABLE #TempTable_PRCS (Servername varchar(50),downtime varchar(50));
END
OPEN sCHEDULER_LIST;
FETCH NEXT FROM sCHEDULER_LIST INTO @name;
WHILE @@FETCH_STATUS = 0
BEGIN
SET @find1 = (SELECT DATEDIFF(MINUTE,S.LASTUPDDTTM, GETDATE())LAST_UPDATE_TIME
FROM PSSERVERSTAT S, PSXLATITEM X
WHERE X.FIELDNAME = 'SERVERSTATUS'
AND X.FIELDVALUE = S.SERVERSTATUS
and S.SERVERNAME =@name
AND DATEDIFF(MINUTE,S.LASTUPDDTTM, GETDATE()) > @timediff)
INSERT INTO #TempTable_PRCS(downtime, Servername ) SELECT DATEDIFF(minute,S.LASTUPDDTTM, GETDATE()), S.SERVERNAME
FROM PSSERVERSTAT S, PSXLATITEM X
WHERE X.FIELDNAME = 'SERVERSTATUS'
AND X.FIELDVALUE = S.SERVERSTATUS
AND S.SERVERNAME = @name
AND DATEDIFF(MINUTE,S.LASTUPDDTTM, GETDATE()) > @timediff
FETCH NEXT FROM sCHEDULER_LIST INTO @name;
END;
SET @tableHTML =
N'<h1>Please check Finance scheduler for issues</h1>'
+ N'<table border="1">'
+ CAST ( ( (
SELECT 'td/@bgcolor' = '#FF3366', td = Servername + 'Scheduler has not been refreshed since ', '' , downtime + ' minutes. Please check.' , ''
--SELECT td = Servername + 'Scheduler has not been refreshed since ', '' , downtime + ' minutes. Please check.' , ''
FROM #TempTable_PRCS
--where Servername = @name
for xml path('tr'), TYPE)
) AS NVARCHAR(MAX) ) +
N'</table>' +
N'<br/>'
+ N'<tr><th><br> </br></th></tr>'
+ N'<tr><th><br> </br></th></tr>'
+ N'<tr><th><br>Thanks!</br></th></tr>'
+ N'<tr><th><br>BusApps Admin.</br></th></tr>'
+ N'<br/>' ;
if (@find1 > @timediff)
BEGIN
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'NSAXBPFNDB01P',
@recipients = 'me@you.com',
@importance = high,
@body = @tableHTML,
@body_format = 'HTML',
@subject = 'Finance Production Process Scheduler Alert' ;
END
CLOSE sCHEDULER_LIST;
DEALLOCATE sCHEDULER_LIST;
