A few days back, while I was working with Alerts for failed scheduled jobs and missing scheduled jobs, I
was needed to exclude running jobs. So we can exactly know which jobs
are actually failed excluding running jobs and sane case for missing
jobs. I applied some additional in the existing script to fetch the records of failed scheduled jobs or missing jobs. Let me share a script here to get all running scheduled jobs,
USE msdb GO CREATE TABLE #RunningJobs ( 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, request_source INT NOT NULL, request_source_id sysname COLLATE database_default NULL, running INT NOT NULL, current_step INT NOT NULL, current_retry_attempt INT NOT NULL, job_state INT NOT NULL ) INSERT INTO #RunningJobs EXECUTE master.dbo.xp_sqlagent_enum_jobs 1,'sa' SELECT sj.name, rj.* FROM #runningjobs rj INNER JOIN dbo.sysjobs sj ON ( sj.job_id = rj.job_id ) WHERE rj.running = 1 DROP TABLE #RunningJobsAbove script use dbo.xp_sqlagent_enum_jobs undocumented object from the master database. It is also useful to get all other state values of jobs like following which received from forums,
0 = Not idle or suspended,I am using this script in failed jobs and missing jobs list. You might be using it somewhere, Please share your comments. I will publish further posts to get failed jobs and missing jobs list.
1 = Executing,
2 = Waiting For Thread,
3 = Between Retries,
4 = Idle,
5 = Suspended,
6 = WaitingForStepToFinish,
7 = PerformingCompletionActions
Comments
Post a Comment