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.
|SQL Server Version||Key First Part|
|SQL Server 2012||MSSQL11|
|SQL Server 2014||MSSQL12|
|SQL Server 2016||MSSQL13|
|SQL Server 2017||MSSQL14|
|SQL Server 2019||MSSQL15|
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)