Need to get Detailed Job History

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Need to get Detailed Job History

Author

Topic

maflynn
Starting Member

5 Posts

Posted - 2006-03-14 : 12:01:00

I am trying to get detailed job history from numerous jobs I am running on my SQL 2000 server. Specifically the type of information you get when you view the job history and click on the Shhow Step Details checkbox. If anyone knows of an sp that will do this, that would be great. I am trying to display more information to non-SQL folks through the use of a web page.

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-03-14 : 12:03:30

How about using SQL Profiler

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2006-03-14 : 12:27:09

How about sp_help_jobhistory?

maflynn
Starting Member

5 Posts

Posted - 2006-03-14 : 13:38:12

sp_help_jobhistory doesn't give the step details. at least that i could find.

quote:
Originally posted by RickD

How about sp_help_jobhistory?

maflynn
Starting Member

5 Posts

Posted - 2006-03-14 : 13:39:25

I don't think i would be able to pull information from SQL profiler from a web page.

quote:
Originally posted by Srinika

How about using SQL Profiler

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-03-14 : 14:19:54

quote:
Originally posted by maflynn

I don't think i would be able to pull information from SQL profiler from a web page.

How about selecting the "Save to File" option and that file is displayed as a web page ?
(I mean placing it in the "web shared" directory)

maflynn
Starting Member

5 Posts

Posted - 2006-03-14 : 14:24:28

I dynamically pull the status of every job i am running. I would like to dynamically create the detailed job history. I don't know of an easy way to dynamically run SQL profiler from a web page. Really all I am trying to find is the error messages for each step that are displayed at the bottom of the job history dialog. I thought they would be in a table but I can't find them.

quote:
Originally posted by Srinika

quote:
Originally posted by maflynn

I don't think i would be able to pull information from SQL profiler from a web page.

How about selecting the "Save to File" option and that file is displayed as a web page ?
(I mean placing it in the "web shared" directory)

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-03-14 : 14:29:37

SQL profiler itself is running dynamically (ie. u don't have to query it)
U can create a trace and let it write to a file. [IT WILL WRITE TO THE FILE WITHOUT U INVOKING IT UNTILL U STOP IT]
U can keep that file in the web server
u can view that file using HTTP protocol

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-03-14 : 14:38:24

I think you're looking for the msdb job tables. All the info in Job History comes from here.


select	j.name
	,js.step_name
	,js.command
	,jh.message
	,jh.run_date
	,jh.run_time
	,jh.run_duration
from	msdb..sysjobs j
join	msdb..sysjobsteps js
		on js.job_id = j.job_id
join	msdb..sysjobhistory jh
		on jh.job_id = js.job_id
		and jh.step_id = js.step_id
where	j.[name] = 'my job name'

EDIT:
I think Rick suggestion is best though:
msdb..sp_help_jobhistory

check this out in books online. @mode='full' should give you step details.

Be One with the Optimizer
TG

maflynn
Starting Member

5 Posts

Posted - 2006-03-14 : 15:09:41

Thanks TG. That is close enough.

quote:
Originally posted by TG

I think you're looking for the msdb job tables. All the info in Job History comes from here.

select j.name
,js.step_name
,js.command
,jh.message
,jh.run_date
,jh.run_time
,jh.run_duration
from msdb..sysjobs j
join msdb..sysjobsteps js
on js.job_id = j.job_id
join msdb..sysjobhistory jh
on jh.job_id = js.job_id
and jh.step_id = js.step_id
where j.[name] = 'my job name'

EDIT:
I think Rick suggestion is best though:
msdb..sp_help_jobhistory

check this out in books online. @mode='full' should give you step details.

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources