监控MS SQL 的Job运行状况的存储过程,很强悍

/****************************************************************************************/ /* Filename: job_monitor.sql */ /* Description: Used to log the start of each job. */ /* Created: Darren Green 20030313 */ /* Amendments: */ /* Name yyyymmdd - Description of amendment */ /****************************************************************************************/ USE msdb IF EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='job_monitor' AND TABLE_TYPE='BASE TABLE') DROP TABLE dbo.job_monitor GO CREATE TABLE dbo.job_monitor ( row_id int NOT NULL IDENTITY, job_id uniqueidentifier NOT NULL, job_name sysname NOT NULL, start_date_time datetime NOT NULL, duration_threshold int NOT NULL, alert_date_time datetime NULL ) ON [PRIMARY] GO ALTER TABLE dbo.job_monitor ADD CONSTRAINT PK_job_monitor PRIMARY KEY CLUSTERED (row_id) ON [PRIMARY] GO ALTER TABLE dbo.job_monitor ADD CONSTRAINT DF_job_monitor_start_date_time DEFAULT (CURRENT_TIMESTAMP) FOR start_date_time GO /****************************************************************************************/ /* Filename: job_monitor_settings.sql */ /* Description: Holds override settings for named and default job. */ /* Created: Darren Green 20030313 */ /* Amendments: */ /* Name yyyymmdd - Description of amendment */ /****************************************************************************************/ USE msdb IF EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='job_monitor_settings' AND TABLE_TYPE='BASE TABLE') DROP TABLE dbo.job_monitor_settings GO CREATE TABLE dbo.job_monitor_settings ( job_name sysname NOT NULL, defult_threshold int NOT NULL, -- Defined threshold value, 0 = No threshold, exclude from monitor ) ON [PRIMARY] GO ALTER TABLE dbo.job_monitor_settings ADD CONSTRAINT PK_job_monitor_settings PRIMARY KEY CLUSTERED (job_name) ON [PRIMARY] GO ALTER TABLE dbo.job_monitor_settings ADD CONSTRAINT DF_job_monitor_settings_defult_threshold DEFAULT (0) FOR defult_threshold GO USE msdb SET QUOTED_IDENTIFIER ON SET ANSI_NULLS ON IF OBJECT_ID('dbo.sp_job_monitor') IS NOT NULL DROP PROCEDURE dbo.sp_job_monitor GO /****************************************************************************************/ /* Filename: sp_job_monitor.sql */ /* Description: Regular job monitor procedure. Clean-up old jobs entries, and */ /* report on anything that has exceeded the threshold. */ /* Created: Darren Green 20030313 */ /* Amendments: */ /* Darren Green 20030502 - Added check to exclude jobs without last run data and */ /* time from causing datetime conversion error, as commented. */ /****************************************************************************************/ CREATE PROCEDURE dbo.sp_job_monitor AS DECLARE @is_sysadmin int DECLARE @job_owner sysname DECLARE @job_id uniqueidentifier DECLARE @duration int DECLARE @duration_threshold int DECLARE @job_name sysname DECLARE @message varchar(8000) DECLARE @subject varchar(255) DECLARE @CrLf char(2) SET @CrLf = CHAR(13) + CHAR(10) -- Get jobs into temp table SELECT @is_sysadmin = ISNULL(IS_SRVROLEMEMBER(N'sysadmin'), 0) SELECT @job_owner = SUSER_SNAME() IF OBJECT_ID('tempdb..#xp_results') IS NOT NULL DROP TABLE #xp_results 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 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 master.dbo.xp_sqlagent_enum_jobs @is_sysadmin, @job_owner -- job_state -- 0 = Not idle or suspended, 1 = Executing, 2 = Waiting For Thread, 3 = Between Retries, 4 = Idle, 5 = Suspended, [6 = WaitingForStepToFinish], 7 = PerformingCompletionActions -- Only want running jobs DELETE #xp_results WHERE ISNULL(running,0) = 0 -- Clean up old jobs that have completed, checking for those with no -- last run date and time first on join. Prevents conversion error. DELETE dbo.job_monitor FROM dbo.job_monitor m LEFT OUTER JOIN #xp_results xp ON xp.job_id = m.job_id AND last_run_date > 0 AND last_run_time > 0 WHERE xp.job_id IS NULL OR CAST(CAST(xp.last_run_date AS char(8)) + ' ' + STUFF(STUFF(REPLICATE('0', 6 - LEN(CAST(xp.last_run_time AS char(6)))) + CAST(xp.last_run_time AS char(8)), 3, 0, ':'), 6, 0, ':') AS datetime) > start_date_time -- Find jobs that have exceeded their threshold DECLARE jobs_cursor CURSOR FAST_FORWARD FOR SELECT m.job_id, DATEDIFF(ss, start_date_time, CURRENT_TIMESTAMP) AS duration, duration_threshold FROM dbo.job_monitor m INNER JOIN #xp_results xp ON xp.job_id = m.job_id WHERE DATEDIFF(ss, start_date_time, CURRENT_TIMESTAMP) > duration_threshold -- Only alert the same job every 15 minutes if overdue AND DATEDIFF(mi, ISNULL(alert_date_time, '1900-01-01 00:00:00'), CURRENT_TIMESTAMP) > 15 OPEN jobs_cursor FETCH NEXT FROM jobs_cursor INTO @job_id, @duration, @duration_threshold WHILE @@FETCH_STATUS = 0 BEGIN SET @job_name = (SELECT name FROM dbo.sysjobs WHERE job_id = @job_id) SET @subject = 'sp_job_monitor - "' + @job_name + '" - long running job' SET @message = 'Job "' + @job_name + '" has been running now for ' + CAST((@duration / 3600) AS varchar(4)) + ' hours, ' + CAST(((@duration / 60) % 60) AS varchar(2)) + ' minutes, ' + CAST((@duration % 60) AS varchar(2)) + ' seconds.' + @CrLf + 'This has exceeded the threshold by ' + CAST(((@duration - @duration_threshold) / 3600) AS varchar(4)) + ' hours, ' + CAST((((@duration - @duration_threshold) / 60) % 60) AS varchar(2)) + ' minutes, ' + CAST(((@duration - @duration_threshold) % 60) AS varchar(2)) + ' seconds.' + @CrLf + 'Threshold is ' + CAST((@duration_threshold / 3600) AS varchar(4)) + ' hours, ' + CAST(((@duration_threshold / 60) % 60) AS varchar(2)) + ' minutes, ' + CAST((@duration_threshold % 60) AS varchar(2)) + ' seconds.' + @CrLf + + '(Duration: ' + CAST(@duration AS varchar(10)) + ', Excess: ' + CAST((@duration - @duration_threshold) AS varchar(10)) + ', Threshold: ' + CAST(@duration_threshold AS varchar(10)) + ')' IF @message IS NULL SET @message = 'Job "' + ISNULL(@job_name, '<NULL>') + '" is now overdue. Formatted message suffered a NULL failure.' PRINT @message EXEC dbo.sp_smtp_sendmail @subject = @subject, @message = @message UPDATE dbo.job_monitor SET alert_date_time = CURRENT_TIMESTAMP WHERE job_id = @job_id FETCH NEXT FROM jobs_cursor INTO @job_id, @duration, @duration_threshold END CLOSE jobs_cursor DEALLOCATE jobs_cursor GO USE msdb SET QUOTED_IDENTIFIER ON SET ANSI_NULLS ON IF OBJECT_ID('dbo.sp_job_monitor_job_start') IS NOT NULL DROP PROCEDURE dbo.sp_job_monitor_job_start GO /****************************************************************************************/ /* Filename: sp_job_monitor_job_start.sql */ /* Description: Procedure added as step to every job to be monitored. Logs the */ /* start of the job for later monitoring. */ /* Created: Darren Green 20030313 */ /* Amendments: */ /* Darren Green 20030328 - Increased to 20% threshhold */ /* Darren Green 20030331 - Rounded to next 5 minute increment */ /*********