We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you a podcast all about Citrix Workspace, moving to the cloud, and analytics & intelligence. Episode 2 coming soon!Listen Now

x

tsql scheduled job info

Medium Priority
234 Views
Last Modified: 2012-05-06
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 ?
Comment
Watch Question

James MurrellQA Product Specialist
CERTIFIED EXPERT

Commented:
unsure. but i get the jobs to write everything to file: and attach the file with the email
James MurrellQA Product Specialist
CERTIFIED EXPERT

Commented:
Data Engineer
CERTIFIED EXPERT
Commented:
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

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Commented:
Can you not just create an operator and notify on fail using the Advanced properties of the job step?

Author

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?
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.