Search code examples
sql-serversql-server-agent

Call log file viewer from T-SQL

I am viewing historic stored procedures in SQL Server by opening "Job Activity Monitor":

enter image description here

Right click on my job and select "View history":

enter image description here

which returns a table, which I then export as a log file

enter image description here

enter image description here

I then proceed to parse the log files in other applications and monitoring purposes.

Is there a way to extract this information using a SQL query, so I can automate this extraction?

I tried EXEC dbo.sp_help_jobhistory @job_id = N'blahblahblah', but the results are not broken into steps.

I tried exec sp_help_jobstep @job_id = N'blahblahblah' to show the steps of the specific job_id, but I do not know how to join this to the table shown above.

Is there a way around this?

Solution

  • You can use:

    EXEC dbo.sp_help_jobhistory @job_id = N'X-Y-Z', @mode='full'

    to get more details the same way SQL Agent does.

    As for your question:

    As an extra, is it possible to see more history than 30 days

    this is a setting for SQL Agent, so you have to control it there: right click on SQL Server Agent -> Properties -> History -> Remove agent history.

    SQL Server Agent history

    Personally, i prefer to use: "Max job history log size" and "maximum job history rows" which ensures every job has decent history

    Note, that it might consume a lot of space if you keep the logs forever :)