:::: MENU ::::

How to start troubleshooting sql server agent failed jobs

  • Jul 30 / 2009
  • 0
dbDigger, SQL Server Agent scheduled Jobs, Troubleshooting

How to start troubleshooting sql server agent failed jobs

SQL Server agent jobs or SQL Server jobs are predefined tasks performed in SQL Server at scheduled time. Any change in permissions or job related objects may cause a job to be failed. It is important that DBA should have a clear idea about how to troubleshoot when job gets failed.
Suppose we have a job TestJob. It has just one task defined in it, that is to select data from sales.salesPerson table in AdventureWorks.

To check last run status of all jobs

Suppose currently we are unaware of status of all of jobs. So first we have to get last run status of all SQL Server agent jobs. For this purpose, double click the Job Activity Monitor in SSMS or right click it to view last run status of all SQL Server scheduled jobs.

check last run status of all jobs

It will provide you a frame with information about last run status of all jobs. I have only two jobs configured so frame has status of just two jobs

last run status of all jobs

Get Details about specific failed job

Job named TestJob has been failed for some reason. Now we have to further investigate several aspects about its failure. For this purpose

  • Go to Jobs folder
  • Right click on failed job
  • Click on View History

Get Details about failed job

It will present previous history of this specific failed jobs. By further drilling any record of failure, you may found further detail and reason of job failure.

Details about failed job

Once you have details and clue about reason, you may further divide the steps execution through executing it through query pane by same user which owns the job. As in our case there is permissions problem for User1 on sales.salesPerson table. User1 owns the job. If we get log in SSMS through User1 and execute the step through query pane, we will get same error.

Get automatically informed about failed jobs

You may automatically get informed and log the job failure in windows event log by following way.

  • Right click on job and go to properties, or simply double click the job
  • Go to notifications section and there you have various intimation options to use

Consult us to explore the Databases. Contact us