SQL SERVER – Enable Additional Logging for SQL Server Agent

When I searched for this topic, I was getting blogs where they were talking about additional logging for the job step in SQL Server Agent jobs. In this blog, I would share my learning about logging about SQL Server Agent itself.

Like the ERRORLOG file for SQL Server, there is are files starting with the name SQLAgent, which are the log files for SQL Server Agent. The default is six older files, the same as SQL ERRORLOG. There is one difference though; the current ERRORLOG has no extension whereas current SQLAgent has an extension of .OUT.

Sometimes, during troubleshooting and issue related to SQL Agent itself or for some learning purposes, we may have a need to increase the information written to SQL Agent logs. There are various methods to do this. All of them end up doing the same thing, modify registry settings so that SQL Agent picks it up during restarts and writes verbose messages.

WORKAROUND/SOLUTION – Additional Logging

METHOD # 1 –

If you want to use T-SQL then you can run the below statement in SSMS or another method in SQL Server. This would also need a restart to the SQL Server agent.

USE [msdb]
GO
EXEC msdb.dbo.sp_set_sqlagent_properties @errorlogging_level=7
GO

METHOD # 2

We can increase the logging by using SQL Server Management Studio (SSMS) also. Here is the screen to do that. This is the properties page of SQL Server Agent and “Include execution trace messages” is the option that needs to be checked.

METHOD # 3

As I mentioned earlier, this all settles down to the registry key. It is a key ErrorLogLevel under HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL15.MSSQLSERVER\SQLServerAgent as show below.

Based on the version and instance name of SQL Server, two parts in the path would change.

MSSQL15 = this is because I have SQL Server 2019. The last two digits would change based on a version of SQL Server.

MSSQLSERVER = This is the instance name given to SQL Server. Since mine is the default instance, its MSSQLSERVER.

Let’s assume that you have SQL Server 2016 and the instance name is PROD then the key would be MSSSQL13.PROD

Once the change is made from any of the methods, the SQL Server Agent service restart is needed to see the change.

Reference: Pinal Dave (https://blog.sqlauthority.com)