Sunday, April 3, 2016

Troubleshooting agent jobs

Administrators always try to automate routine tasks, making sure that all required operations are done on time without failures. The main component used for automation is SQL Server Agent, which is used for creating jobs. If you have noticed that a scheduled job has not run or all jobs are not running, how do you troubleshoot? How do you start troubleshooting?

Here are some guidelines for troubleshooting based on a discussion I had;
  • First thing needs to be checked is whether SQL Server Agent service is running or not. You need to make sure that Startup type is set to automatic, this makes sure that service is started when the server is restarted. If, for some reasons, service is not running, and you cannot even manually start it, check following;
    • Check and see whether account assigned for the Agent is valid and no issues with the password. The account assigned may have been expired, changed or disabled. Check the system log for more details.
    • Check the msdb database. If it is corrupted or offline, Agent will not be started.
  • Check the job history. Check whether last run was successful. There can be some issues with business logic implemented.
  • Check whether the job is enabled. Someone might have disabled it.
  • Check whether the schedule set is either expired or disabled.
  • Check and see whether proxy accounts are working properly if you have used. Check credentials used for proxy accounts.
  • Check dependencies. There can be steps in the job that run without any issues but some. Check whether all required items, such as files, folders and all required permissions for accessing are available.

No comments: