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.
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
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
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.
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