Which system tables gives SQL Agent jobs start time and stop time in SQL Server 2000

Question

Post reply

Which system tables gives SQL Agent jobs start time and stop time in SQL Server 2000

sree-879715

SSCommitted

Points: 1794
More actions
January 5, 2010 at 11:37 am

#156550

Hi,
I know that msdb.dbo.sysjobactivity table gives me all the SQL Agent jobs start_execution_time and stop_execution_time in SQL Server 2005.
This table is not there in SQL Server 2000. Can anyone tell me which other table in SQL 2000 gives me such information.
Thank You in Advance,
Sree

Viewing 10 posts - 1 through 9 (of 9 total)

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

Lynn Pettis

SSC Guru

Points: 442467

January 5, 2010 at 11:49 am

#1099126

Try sysjobschedules.

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

You can get pretty much the same information from the sysjobhistory table, which is in both SQL 2000 and SQL 2005. Look at run_date, run_time, and run_duration.

If you're interested in current rather than historical data, use sysjobschedules as Lynn suggested.

Greg

sree-879715 SSCommitted Points: 1794 More actions · Answer 3

Hi,

Thanks for the replies !!!

The sysjobschedules doesn't provide me when the job started and finished information. Also, I am not looking for the next run date or last run date information.

I need the table or something which gives me the information like :

When actually the SQL Agent job started and what time it finished.

This information I can get in SQL Server 2005 by quering the sysjobacitivity table with the start_execution_time and stop_execution_time columns.

Regards,

Sree

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

You'll have to use the sysjobhistory table and compute the end date/time using the run_date, run_time, and run_duration columns.

sree-879715 SSCommitted Points: 1794 More actions · Answer 5

Thank you Lynn for your reply

I will work on this and will update again if I have any other issues.

Thank You Again,

Sree

MANU-J. SSC-Dedicated Points: 31126 More actions · Answer 6

MANU-J.

SSC-Dedicated

Points: 31126

January 6, 2010 at 12:34 pm

#1099861

Please try the attached SP.

MJ

sree-879715 SSCommitted Points: 1794 More actions · Answer 7

Hi Manu,

Thank you very much for providing me with the SP.

I have a crazy doubt, please correct me if I am wrong.

Is the date and time shows the time at which the job finished executing ?

Regards,

Sree

MANU-J. SSC-Dedicated Points: 31126 More actions · Answer 8

Is the date and time shows the time at which the job finished executing ?

Yep.

Manu

sree-879715 SSCommitted Points: 1794 More actions · Answer 9

Thank you once agian for your valuable SP.

I was in the middle of writing a SP to get the job start time and end time in SQL Server2000, now I can use your SP and modify it to get the result what I am looking for.

Regards,

Sree