USE [master] GO /****** Object: StoredProcedure [dbo].[up_Job_Duration_Alert] Script Date: 05/16/2012 17:17:16 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[up_Job_Duration_Alert] AS BEGIN SELECT sj.name, sja.run_requested_date, sja.start_execution_date, ISNULL(CONVERT(nvarchar(20),sja.stop_execution_date,121),'Running') [stop_execution_date], ISNULL(CONVERT(VARCHAR(12), (sja.stop_execution_date-sja.start_execution_date), 108),'Running') [Duration], CONVERT(VARCHAR(12), GETDATE()-sja.start_execution_date, 108) [Exec_Time] into #jobs FROM msdb.dbo.sysjobactivity sja INNER JOIN msdb.dbo.sysjobs sj ON sja.job_id = sj.job_id WHERE sja.run_requested_date IS NOT NULL AND sj.description LIKE '%JobDurationControl = ENABLE%' AND sj.enabled = 1 AND sja.stop_execution_date IS NULL AND sja.start_execution_date > getdate() - 30 -- eski yıllardan kalan stopsuz jobları elemek için ORDER BY sja.run_requested_date DESC --Query sonucunu mail atmasy için html formatta tablo olu?turma DECLARE @tableHTML NVARCHAR(MAX) ; SET @tableHTML = N'
MAINTANENCE JOBLAR UZUN SUREDIR RUN DURUMUNDA
' + N'
' + N'
Job Name
'+ N'
Schedule_Date
'+ N'
Exec_Start_Date
' + N'
Exec_Stop_Date
' + N'
Duration
' + N'
Exec_Time
' + CAST ( ( SELECT DISTINCT td = name, '', td = run_requested_date, '', td = start_execution_date, '', td = stop_execution_date, '', td = Duration, '', td = Exec_Time, '' FROM #jobs WHERE Exec_Time > '04:00:00:000' FOR XML PATH('tr'), TYPE ) AS NVARCHAR(MAX) ) SELECT * FROM #jobs WHERE Exec_Time > '04:00:00:000' IF @@ROWCOUNT>0 BEGIN --query sonucunu mail atan bölüm EXEC msdb.dbo.sp_send_dbmail @recipients='[email protected]', @subject = 'HALEN DEVAM EDEN MAINTANENCE JOBLAR', @body = @tableHTML, @body_format = 'HTML'; END; DROP TABLE #jobs END
