How to check SQl jobs failures in SQl server

To check for SQL Server job failures, you can use SQL Server Management Studio (SSMS) and review the job history. Here's how you can do it:

1. Using SQL Server Management Studio (SSMS):

   • Open SQL Server Management Studio.
   • Connect to the SQL Server instance where the jobs are running.
   • In the Object Explorer, expand the "SQL Server Agent" node.
   • Expand the "Jobs" node to see the list of jobs.
   
   
   Object Explorer

   
   

      - Right-click on the job for which you want to check the history and select "View History."

   
   

   View History

   
   

      - The "Job Activity Monitor" will open, and you can see the job history. Look for any entries with a status of "Failed."

   
   

   Job Activity Monitor

   
   

      - You can also review the "Message" column for more details about the failure.

   
   

2. Using T-SQL:

   Alternatively, you can use T-SQL queries to retrieve information about job failures. Here's an example query:

   USE msdb;
   GO
   
   SELECT
       job.name AS 'JobName',
       job.job_id AS 'JobID',
       jobhistory.run_status AS 'RunStatus',
       jobhistory.run_date AS 'RunDate',
       jobhistory.run_duration AS 'RunDuration',
       jobhistory.message AS 'Message'
   FROM
       dbo.sysjobs job
   INNER JOIN
       dbo.sysjobhistory jobhistory ON job.job_id = jobhistory.job_id
   WHERE
       jobhistory.run_status = 0 -- 0 indicates failure
   ORDER BY
       jobhistory.run_date DESC;
   

   This query retrieves information about failed job runs, including the job name, ID, run status, run date, run duration, and error message.

Remember to replace 0 with the appropriate status code if your SQL Server version uses different status codes for job failures. Also, ensure you have the necessary permissions to access the job history information.

 More details-- https://sqlstudies.com/2015/10/07/finding-the-error-from-a-failed-job/