Monday, December 26, 2016

SQL Server Agent Error are logged in SQL Server Error Log or somewhere else?

I have written two posts on SQL Server Error Log; How to delete current SQL Server error log? My current log is in GBs and Reading SQL Server Error Log using XP_READERRORLOG, and they explain how the log is maintained, how log files are created and how the content can be read. I was asked a question on this but not exactly on SQL Server errors but SQL Server Agent errors.

Can we read/see SQL Server Agent errors in SQL Server Error Logs? Or should we see somewhere else for this? Generally, you should see all errors related to SQL Server but errors related to Agent are maintained somewhere else called Agent Error Log.

Just like the way SQL Server maintains its error logs, multiple files are maintained for Agent error logs. By default there are 10 files and they are saved in Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Log folder.

The file named as SQLAGENT.OUT is the current one and just like the SQL Server Error logs, this gets recycled when the service is restarted. Once recycled, current one becomes SQLAGENT.1 and old SQLAGENT.1 becomes SQLAGENT.2, and so on. However, using Congfigure menu option in SQL Server Error Log node, we can adjust the number of files to be maintained but that facility is not exist with Agent Error Log.

If you see that the current log file is larger than you expect, it can be recycled by either by clicking the Recylce menu item in the context menu (see above image) or executing dbo.sp_cycle_agent_errorlog stored procedure.

No comments: