How to see one week before job history

Question

How to see one week before job history

cc 80999

SSC Veteran

Points: 289
More actions
June 20, 2011 at 2:31 pm

#145822

Hello:
I have a agent job running. When I right click this job to view history, only show me current day job history. How to see one week ago job history?
Thanks

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Greg Charles SSC-Forever Points: 45403 More actions · Answer 1

Maybe the SQL Agent job history log size is set too small. In SSMS, Right-click on SQL Server Agent and select Properties. Select History and adjust the maximum job history log size and rows per job.

Greg

cc 80999 SSC Veteran Points: 289 More actions · Answer 2

Thanks.

Is any script to get detailed failed job step a week ago from sys archive table like view history gui

cc 80999 SSC Veteran Points: 289 More actions · Answer 3

Hello:

I run this script

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 > 2011-06-15

ORDER BY h.instance_id DESC

I have failed job, but I did not get any result. Where I missed. Thanks

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 4

may be some tweak required here AND h.run_date > 2011-06-15

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 5

get a copy of the awesome free tool SQLJOBVIS. Shows agent history as a gantt chart. Great for finding overlaps!

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

John Waclawski SSCommitted Points: 1505 More actions · Answer 6

TheSQLGuru (6/22/2011)
get a copy of the awesome free tool SQLJOBVIS. Shows agent history as a gantt chart. Great for finding overlaps!

Be leary about this SQLJOBVIS web page. Google flagged it as suspicious due to malware being put on your web page when you visit it.

Too bad. The software looks very helpful and to boot it's free. Wish they'd clean up their web page.

hcprasadv Ten Centuries Points: 1215 More actions · Answer 7

Set up the Job History Log by time period

Connect to Sql Server Instance and right click the Sql Server Agent and select the Properties page. In the SQL Server Agent Properties dialog box select the History page.

Choose Automatically remove agent history, and specify a time period, history older than this period will be purged from the log.