How to find out error logs, Job History of SQL Server Agent jobs.

Problem: How to find out error logs, Job History of SQL Server Agent jobs.

Solution: SQL Server Agent Tables (Transact-SQL)

SQL Server Agents are generally used for scheduling jobs to run a SSIS package or Scheduled maintenance task using T-SQL or Commands.

They write back this Scheduled jobs information into system database (msdb), they are further categorized into SQL Server agent job tables.

There are couple of SQL server Agent tables which will give us the specified contextual meaning of the job history and job steps, which are listed below.

dbo.sysjobs -Contains the name, description of a job information.

dbo.sysjobsteps - Contains the information for each step in a job to be executed by SQL Server Agent.

dbo.sysjobhistory -Contains Job history.

dbo.sysjobstepslogs -Contains information about job step logs.

Example:

In this article I am going to show you how we can retrieve list of steps, history and step logs of a job.

Example 1:

I am going to create a SQL Server agent job and it basically has two steps and then to make some activity I will run it for a few times and then show you the details.

SELECT sj.NAME,

                sj.description,

                js.server,

                js.database_name,

                js.database_user_name,

                js.step_id,

                js.step_name,

                jh.run_date,

                Jh.run_time,

                jh.run_duration,

                jsl.log_size as size_of_the_job_log_bytes

FROM msdb.dbo.sysjobs sj

INNER JOIN msdb.dbo.sysjobsteps JS

                ON js.job_id = sj.job_id

INNER JOIN msdb.dbo.sysjobhistory JH

                ON JS.job_id = JH.job_id

                                AND JS.step_id = JH.step_id

LEFT JOIN msdb.dbo.sysjobstepslogs JSL

                ON JS.step_uid = JSL.step_uid

               

 OUPTUT:

Featured_script:

/*

this script requires a valid parameter @JOBname the job name to retrive the jobid of a SQL Server Agent Job.

This results the information related to failures on the scheduled SQL Server Agent jobs

*/

DECLARE @job_id UNIQUEIDENTIFIER,

                @jobname SYSNAME = 'Purge History'

SELECT @job_id = job_id

FROM msdb.dbo.sysjobs

WHERE [name] = @JobName

SELECT sj.name as job_name,

                JS.step_name

                ,'Step ' + CAST(JH.step_id AS CHAR(3)) + ' of ' + CAST(MAX(js.step_id) OVER (ORDER BY (SELECT 0)) as char(3)) AS job_Failed_at_Step,

[RunDateTime] = msdb.dbo.agent_datetime(jh.run_date,jh.run_time),
 JH.run_duration,

                CASE                      WHEN JSL.[log] IS NULL                                 THEN JH.[Message]

                                                                                ELSE JSL.[log]                     END AS LogOutput

FROM msdb.dbo.sysjobs sj

INNER JOIN msdb.dbo.sysjobsteps JS

ON js.job_id=sj.job_id

INNER JOIN msdb.dbo.sysjobhistory JH

                ON JS.job_id = JH.job_id

                                AND JS.step_id = JH.step_id

LEFT JOIN msdb.dbo.sysjobstepslogs JSL

                ON JS.step_uid = JSL.step_uid

WHERE

                JS.step_id <> 0

                AND JH.job_id = @job_id

                AND JH.run_status = 0

ORDER BY Jh.run_date desc,Jh.run_time desc

To know more about how to create a SQL Server Agent job, please read.. SQL Server Agent Jobs