December 10, 2012 at 6:30 pm
Hi all,
Does anyone have any scripts to find a list of all the failed jobs in the last 10 hours.
I know I can find all failed jobs from sys.jobhistory.....but how can modify the script to get failed jobs for the past 10 hours? Please advise.
Thanks,
SueTons.
Regards,
SQLisAwe5oMe.
December 10, 2012 at 9:58 pm
Hi Sue,
Please find the below steps to find the job failed in sql sever 2005
-- Variable Declarations
DECLARE @PreviousDate datetime
DECLARE @Year VARCHAR(4)
DECLARE @Month VARCHAR(2)
DECLARE @MonthPre VARCHAR(2)
DECLARE @Day VARCHAR(2)
DECLARE @DayPre VARCHAR(2)
DECLARE @FinalDate INT
-- Initialize Variables
SET @PreviousDate = DATEADD(dd, -7, GETDATE()) -- Last 7 days
SET @Year = DATEPART(yyyy, @PreviousDate)
SELECT @MonthPre = CONVERT(VARCHAR(2), DATEPART(mm, @PreviousDate))
SELECT @Month = RIGHT(CONVERT(VARCHAR, (@MonthPre + 1000000000)),2)
SELECT @DayPre = CONVERT(VARCHAR(2), DATEPART(dd, @PreviousDate))
SELECT @Day = RIGHT(CONVERT(VARCHAR, (@DayPre + 1000000000)),2)
SET @FinalDate = CAST(@Year + @Month + @Day AS INT)
-- Final Logic
SELECT j.[name],
s.step_name,
h.step_id,
h.step_name,
h.run_date,
h.run_time,
h.sql_severity,
h.message,
h.server
FROM msdb.dbo.sysjobhistory h
INNER JOIN msdb.dbo.sysjobs j
ON h.job_id = j.job_id
INNER JOIN msdb.dbo.sysjobsteps s
ON j.job_id = s.job_id
AND h.step_id = s.step_id
WHERE h.run_status = 0 -- Failure
AND h.run_date > @FinalDate
ORDER BY h.instance_id DESC
Please let me know further concerns
Regards
Vivek
December 12, 2012 at 2:00 pm
I use this script to check for failed SQL Agent jobs. You can easily modify it for 10 hours or whatever time interval you need to audit.
It's nothing fancy but it gets the job done.
-- Audit Query - Failed Jobs Report
DECLARE @PreviousDate varchar(8)
DECLARE @FinalDate varchar(8)
-- Initialize Variables
SET @PreviousDate = (CONVERT(varchar(8), DATEADD(dd, -1, GETDATE()), 112)); -- Yesterday
SELECT @PreviousDate AS [Start Date]
SET @FinalDate = (CONVERT(varchar(8), DATEADD(dd, +1, GETDATE()), 112)); -- Tomorrow
SELECT @FinalDate AS [End Date]
-- Identify failed jobs within last day
SELECT *
FROM msdb.dbo.sysjobhistory h
INNER JOIN msdb.dbo.sysjobs j
ON h.job_id = j.job_id
INNER JOIN msdb.dbo.sysjobsteps s
ON j.job_id = s.job_id
INNER JOIN (SELECT DISTINCT job_id FROM msdb..sysjobhistory WHERE run_status <> 1) a
ON h.job_id = a.job_id
WHERE h.run_status = 0 -- Failure
AND h.run_date >= @PreviousDate AND h.run_date < @FinalDate
December 12, 2012 at 2:12 pm
Thanks to both of you. Appreciate it.
SueTons.
Regards,
SQLisAwe5oMe.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply