• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 215
  • Last Modified:

tsql scheduled job info

Im using stored procedure sp_help_jobhistory to identify failed jobs and email them to me. i would like to include the step that failed and the message from the job history but im not sure where this info is stored ?
0
9772885
Asked:
9772885
1 Solution
 
James MurrellProduct SpecialistCommented:
unsure. but i get the jobs to write everything to file: and attach the file with the email
0
 
James MurrellProduct SpecialistCommented:
0
 
SharathData EngineerCommented:
check these scripts.
-- Failed jobs report. This query lists the name of all jobs that failed in their last attempt which are enabled:
SELECT name FROM msdb.dbo.sysjobs A, msdb.dbo.sysjobservers B WHERE A.job_id = B.job_id AND B.last_run_outcome = 0 and enabled = 1
 
-- Disabled jobs. ever have a critical job that someone decided to disable? Catch it with this script!
SELECT name FROM msdb.dbo.sysjobs WHERE enabled = 0 ORDER BY name
 
------ Job information
SELECT a.name AS job_name
       a.enabled,
       a.description,
       a.date_created,
       a.date_modified,
       b.last_outcome_message,
       b.last_run_date,
       b.last_run_time,
       b.last_run_duration,
       convert(nvarchar(10),(b.last_run_duration/10000)) + ':' + 
       convert(nvarchar(10),((b.last_run_duration%10000)/100)) + ':' + 
       convert(nvarchar(10),(((b.last_run_duration%10000)%100)%100)) as duration,
       s.next_run_date,
       s.next_run_time,
       v.name,
       v.freq_type,
       v.freq_subday_type,
       v.freq_interval
  FROM MSDB.dbo.sysjobs AS a
  JOIN MSDB.dbo.sysjobservers AS b ON a.job_id = b.job_id
  JOIN MSDB.dbo.syscategories AS c ON a.category_id = c.category_id
  JOIN MSDB.dbo.sysjobschedules AS S ON A.job_id = s.job_id
  JOIN MSDB.dbo.sysschedules_localserver_view AS v ON s.schedule_id = v.schedule_id
 WHERE (c.name <> 'Report Server' ) --AND a.name LIKE 'Mars%'
 ORDER BY job_name 

Open in new window

0
 
reb73Commented:
Can you not just create an operator and notify on fail using the Advanced properties of the job step?
0
 
9772885Author Commented:
ive had a look at the link which is very useful and although i can see i way of tracking individual steps on whether they are executing i cant see one for the overall job. I guess i could assmue that if any of the steps are executing then the job is but it would be nice of this was summerised somewhere else?
0

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now