Troubleshooting the SQL Server Agent Error Log

SQL Server Agent creates an error log. This error log, by default, records errors and warnings. When SQL Server Agent is stopped, you can modify the location of the error log. When the SQL Server error log is empty, it is not displayed.

Three types of messages are displayed in the SQL Server Agent error log.

• Informational messages, which provide general condition messages, such as “Job Test has been requested to run by startup sequence 0.”

  By default, informational messages are not written to the SQL Server Agent error log because they can fill it, thereby reducing your ability to select and analyze more difficult errors. As the SQL Server Agent error log adds an additional processing load to the server, consider what value you attain by capturing informational messages to this error log. Generally, it is best to capture all messages only when you are debugging a specific problem.

  To write informational messages to the error log, in the SQL Server Agent Properties dialog box, select Include execution trace messages.

• Warning messages, that provide information about potential problems such as, “Job test was deleted while it was executing.”
• Error messages, that usually require intervention by a system administrator to resolve such as, “Unable to start mail session.” Error messages can be sent to a specific user or computer by network popup.

SQL Server maintains up to nine SQL Server Agent error logs. Each archived error log has an extension indicating the age of the error log. For example, an extension of .1 indicates the newest archived error log and an extension of .9 indicates the oldest archived error log.

To rename a SQL Server Agent error log

To write execution trace messages to the SQL Server Agent error log

To view the SQL Server Agent error log

To send SQL Server Agent error messages

See Also