March 8, 2018 at 2:43 am
Hi,
Is there a sql query that I can run that will give the same detail as what is on Job Activity Monitor?
So information like:
Name (Job Name)
Enabled
Status
Last Run Outcome
Last Run
Next Run
Scheduled
Also I want to see information within that Joib Name like :
Step No,
Step Name,
subsystem (SSIS, T-SQL)
Package Name (SSIS Package it is running)
Scheduled TImes for the Jobs as well
Thanks
March 8, 2018 at 3:28 am
SQL_Kills - Thursday, March 8, 2018 2:43 AMHi,Is there a sql query that I can run that will give the same detail as what is on Job Activity Monitor?
So information like:
Name (Job Name)
Enabled
Status
Last Run Outcome
Last Run
Next Run
Scheduled
Have you tried sp_who2? If that's not close enough, run a trace while you start Activity Monitor so you can capture the exact SQL that's executed.
Also I want to see information within that Joib Name like :
Step No,
Step Name,
subsystem (SSIS, T-SQL)
Package Name (SSIS Package it is running)
Scheduled TImes for the Jobs as well
Sounds almost like homework or an interview question, this. You can get all that information from the sysjobs and sysjobsteps tables in msdb.
John
March 8, 2018 at 4:34 am
The information form the "Job Activity" monitor can be queried from the tables in the [msdb] database. Join the [sysjobs] and [sysjobsteps] tables to get the details of the job itself. Information about the schedule of a job can be found in [sysschedules].But to rebuild this to a usable output of how each job is scheduled is quite extensive, so I advise you to only include this if you really need it. Instead the [sysjobschedules] table will give you the moment of the next run in an easy format. You can also include a join to the [sysjobhistory] table to get the information about the times the job has run. There is a sample on the Microsoft Docs site to rebuild the run date and time to humanly readable values.
SET NOCOUNT ON;
SELECT sj.name,
sh.run_date,
sh.step_name,
STUFF(STUFF(RIGHT(REPLICATE('0', 6) + CAST(sh.run_time as varchar(6)), 6), 3, 0, ':'), 6, 0, ':') 'run_time',
STUFF(STUFF(STUFF(RIGHT(REPLICATE('0', 8) + CAST(sh.run_duration as varchar(8)), 8), 3, 0, ':'), 6, 0, ':'), 9, 0, ':') 'run_duration (DD:HH:MM:SS) '
FROM msdb.dbo.sysjobs sj
JOIN msdb.dbo.sysjobhistory sh
ON sj.job_id = sh.job_id
March 9, 2018 at 3:15 pm
SQL_Kills - Thursday, March 8, 2018 2:43 AMHi,Is there a sql query that I can run that will give the same detail as what is on Job Activity Monitor?
So information like:
Name (Job Name)
Enabled
Status
Last Run Outcome
Last Run
Next Run
ScheduledAlso I want to see information within that Joib Name like :
Step No,
Step Name,
subsystem (SSIS, T-SQL)
Package Name (SSIS Package it is running)
Scheduled TImes for the Jobs as wellThanks
As already mentioned you can get some of the information from the tables in msdb as to how it's scheduled, last history, etc. To get information on jobs that are currently running, you could use sp_get_composite_job_info. What you see in job activity monitor is a process of building the output using some of the tables, stored procedures/extended stored procedures that are dumped into temp tables. The last thing executed to for the job activity is sp_help_job which has a few different result sets. In the past and still somewhat these days, a lot of people would use xp_sqlagent_enum_jobs which has more information on current activity and tends to be a bit more accurate as it also grabs information from SQL Agent caches. A lot of current execution information is in the cache and then written to the tables.
If you want to pick apart where different pieces of information come from you can follow the path of sp_help_job which calls sp_get_composite_job_info and that in turn uses sysjobs_view and xp_sqlagent_enum_jobs for the composite job information.
Sue
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply