Presents your SQL SERVER E-NEWSLETTER for January 14, 2003 <-------------------------------------------> PROVE THAT A JOB IS EXECUTING ON SCHEDULE How can you determine or prove that a job is not executing at every scheduled interval if the job is taking longer than the scheduled interval? For example, say you need to verify a job scheduled to run every hour because a manager thinks it's taking longer than an hour; the manager believes the job is being run a second time before the first one completes. This is where querying the system tables in msdb can help. SQL Agent has the intelligence built in to accommodate this scenario. The following steps demonstrate how to query msdb system tables to prove that SQL Agent is not allowing jobs to overrun themselves: -- Create job: BEGIN TRANSACTION DECLARE @JobID BINARY(16) DECLARE @ReturnCode INT SELECT @ReturnCode = 0 IF (SELECT COUNT(*) FROM msdb.dbo.syscategories WHERE name = N'ToBeDeleted') < 1 EXECUTE msdb.dbo.sp_add_category @name = N'ToBeDeleted' -- Delete the job with the same name (if it exists) SELECT @JobID = job_id FROM msdb.dbo.sysjobs WHERE (name = N'Test Job Schedule Overlap') IF (@JobID IS NOT NULL) BEGIN -- Check if the job is a multi-server job IF (EXISTS (SELECT * FROM msdb.dbo.sysjobservers WHERE (job_id = @JobID) AND (server_id <> 0))) BEGIN -- There is, so abort the script RAISERROR (N'Unable to import job ''Test Job Schedule Overlap'' since there is already a multi-server job with this name.', 16, 1) GOTO QuitWithRollback END ELSE -- Delete the [local] job EXECUTE msdb.dbo.sp_delete_job @job_name = N'Test Job Schedule Overlap' SELECT @JobID = NULL END BEGIN -- Add the job: EXECUTE @ReturnCode = msdb.dbo.sp_add_job @job_id = @JobID OUTPUT , @job_name = N'Test Job Schedule Overlap', @owner_login_name = N'sa', @description = N'No description available.', @category_name = N'ToBeDeleted', @enabled = 1, @notify_level_email = 0, @notify_level_page = 0, @notify_level_netsend = 0, @notify_level_eventlog = 2, @delete_level= 0 IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback -- Add the job steps: EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID, @step_id = 1, @step_name = N'WaitForLongerThanOneMinute', @command = N'DECLARE @T INT SELECT @T = CONVERT(INT, SUBSTRING ( CONVERT(CHAR(3), DATEPART(MS,CURRENT_TIMESTAMP)) , 2,2)) WAITFOR DELAY @T', @database_name = N'master', @server = N'', @database_user_name = N'', @subsystem = N'TSQL', @cmdexec_success_code = 0, @flags = 0, @retry_attempts = 0, @retry_interval = 1, @output_file_name = N'', @on_success_step_id = 0, @on_success_action = 1, @on_fail_step_id = 0, @on_fail_action = 2 IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback EXECUTE @ReturnCode = msdb.dbo.sp_update_job @job_id = @JobID, @start_step_id = 1 IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback -- Add the job schedules: EXECUTE @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id = @JobID, @name = N'EveryMinute', @enabled = 1, @freq_type = 4, @active_start_date = 20021102, @active_start_time = 0, @freq_interval = 1, @freq_subday_type = 4, @freq_subday_interval = 1, @freq_relative_interval = 0, @freq_recurrence_factor = 0, @active_end_date = 99991231, @active_end_time = 235959 IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback -- Add the Target Servers: EXECUTE @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @JobID, @server_name = N'(local)' IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback END COMMIT TRANSACTION GOTO EndSave QuitWithRollback: IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION EndSave: GO -- Query system tables: use msdb go select case his.run_status when 0 then 'failed' when 1 then 'succeeded' when 2 then 'retry' when 3 then 'canceled' when 4 then 'in progress' end status, his.run_date, substring ( stuff ( his.run_time, 1,0, REPLICATE('0',6 - LEN(his.run_time) )) , 1, 2) + ':' + substring ( stuff ( his.run_time, 1,0, REPLICATE('0',6 - LEN(his.run_time) )) , 3, 2) + ':' + substring ( stuff ( his.run_time, 1,0, REPLICATE('0',6 - LEN(his.run_time) )) , 5, 3) run_time2, substring ( stuff ( his.run_duration, 1,0, REPLICATE('0',6 - LEN(his.run_duration) )) , 1, 2) + ':' + substring ( stuff ( his.run_duration, 1,0, REPLICATE('0',6 - LEN(his.run_duration) )) , 3, 2) + ':' + substring ( stuff ( his.run_duration, 1,0, REPLICATE('0',6 - LEN(his.run_duration) )) , 5, 3) run_duration, case when run_duration > 100 then '<------' else '' end OverOneMinuteSchedule, substring(j.name,1,40) job_name from sysjobhistory his join sysjobs j on his.job_id = j.job_id join syscategories cat on j.category_id = cat.category_id where cat.name = 'ToBeDeleted' and his.step_id = 1 order by his.run_date desc, his.run_time asc go The first step needed is to create a job that is scheduled to execute every minute. The command is running a delay command for varying amounts of time. The command is converting the last two digits of the milliseconds of the current time and using them as an argument for the WAITFOR DELAY command. The last two digits of the milliseconds are being interpreted as seconds for the WAITFOR DELAY command. The second step is to query the msdb database system tables to retrieve the information on run_time and run_duration. Here is the sample output from the query: status run_date run_time run_duration OverOneMinuteSchedule job_name -------------------------------------------------------------------------- succeeded 20021103 15:03:00 00:00:01 Test Job Schedule Overlap succeeded 20021103 15:04:00 00:00:08 Test Job Schedule Overlap succeeded 20021103 15:05:01 00:00:07 Test Job Schedule Overlap succeeded 20021103 15:06:00 00:00:01 Test Job Schedule Overlap succeeded 20021103 15:07:01 00:00:00 Test Job Schedule Overlap succeeded 20021103 15:08:01 00:00:03 Test Job Schedule Overlap succeeded 20021103 15:09:01 00:00:03 Test Job Schedule Overlap succeeded 20021103 15:10:01 00:00:03 Test Job Schedule Overlap succeeded 20021103 15:11:01 00:00:03 Test Job Schedule Overlap succeeded 20021103 15:12:00 00:01:33 Test Job Schedule < -- Overlap succeeded 20021103 15:14:00 00:01:33 Test Job Schedule < -- Overlap succeeded 20021103 15:16:00 00:01:37 Test Job Schedule < -- Overlap succeeded 20021103 15:18:00 00:00:00 Test Job Schedule Overlap succeeded 20021103 15:19:00 00:00:00 Test Job Schedule Overlap succeeded 20021103 15:20:00 00:00:17 Test Job Schedule Overlap succeeded 20021103 15:21:00 00:00:00 Test Job Schedule Overlap succeeded 20021103 15:22:00 00:00:00 Test Job Schedule Overlap succeeded 20021103 15:23:00 00:00:00 Test Job Schedule Overlap succeeded 20021103 15:24:00 00:00:03 Test Job Schedule Overlap succeeded 20021103 15:25:00 00:00:03 Test Job Schedule Overlap succeeded 20021103 15:26:00 00:00:03 Test Job Schedule Overlap succeeded 20021103 15:27:00 00:00:50 Test Job Schedule Overlap succeeded 20021103 15:28:00 00:01:10 Test Job Schedule < -- Overlap succeeded 20021103 15:30:00 00:01:10 Test Job Schedule < -- Overlap succeeded 20021103 15:32:00 00:00:57 Test Job Schedule Overlap The column labeled OverOneMinuteSchedule will display an arrow for run times that had a duration longer than the scheduled one minute interval. Study the run_time and run_duration columns, and you'll see that the job did not run another session before the duration of the first job completed. ----------------------------------------