SQL SERVER – Enable Additional Logging for SQL Server Agent

SQL
1 Comment

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.

SQL SERVER - Enable Additional Logging for SQL Server Agent logging-800x135

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.

Solarwinds
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.

SQL SERVER - Enable Additional Logging for SQL Server Agent sqlagt-log-01

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.

SQL SERVER - Enable Additional Logging for SQL Server Agent sqlagt-log-02

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

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)

Solarwinds
, , , ,
Previous Post
SQL SERVER – SqlServerWriter Missing in vssadmin List Writers Command
Next Post
SQL SERVER – Always On Listener Creation Failure – Enabling Object ProdListener Failed With Error 5

Related Posts

1 Comment. Leave new

  • Logs are so important. Today at work I kept getting a error when executing any query that my connection was lost to the server. So I went to the log and found that there was a brute force attack currently happening trying to login using the SA account(lucky we disabled the SA account so it was a pointless attempt) I called up our network admin and he found the hole and shut it down.

    Reply

Leave a Reply

Menu