Getting SQL Job Execution Time History

• Posted by justin on January 10, 2005
• 0 comments

I was asked today about how long the MS sql jobs in one of my applications took to run.  After some research I created the following script.  This script will look thru all of the jobs for the sql server and give the execution history summary. 

DECLARE @Name varchar(255)

DECLARE My_Cursor CURSOR FOR
select name
from msdb.dbo.sysjobs j
OPEN Fuzion_Cursor

FETCH NEXT FROM My_Cursor
INTO @Name
WHILE @@FETCH_STATUS = 0
BEGIN
EXECUTE MSDB.DBO.SP_HELP_JOBHISTORY NULL,
 @Name
, @MODE = N'SUMMARY'
 , @step_id = 0
 , @run_status = 1

FETCH NEXT FROM My_Cursor 
   INTO @Name
END

CLOSE My_Cursor
DEALLOCATE My_Cursor