Search code examples
sql-serverjobs

Is there any events after SQL Server job finished?

I want to get the latest raw job history and save them in my format after job finished every times. I have written a stored procedure to get the history with sp_help_jobhistory, then format the result, then save them into a new table.

But, when to call this stored procedure?

Is there some event fired when the job finishes?

Maybe there are some others solutions.

Thanks for your comments/answers.

Solution

  • I have tried the solution, and it works well. Step 1: Will call sp BeginHistoryLog, this sp will get the information of job from [msdb].[dbo].[sysjobs] tabel by job name. And this SP will write the initial data intto JobHistory which log the history message.

    Step 2: Will call the sp which do the actual work.

    Step 3: Will call SP EndHistoryLog, this SP will get the step2 execution information from msdb.dbo.sysjobhistory and INNER JOIN [msdb].[dbo].[sysjobs] by job id and step id. And this sp will write the execution inforamtion into JobHistory table.