SQL Server Agent Logs

SQL Server Agent Logs are useful in troubleshooting issues with the database server. It contains both system and user-defined events information. We can use error logs in the following scenario:

  • Information about SQL Server instance
  • Audit login – Success or failure
  • Database recovery status
  • Logged errors messages

We can read the error logs using different methods, but firstly, we should know the path of the error logs.

/*
SQL Server error log offers good way to obtain information when troubleshooting SQL Server related problem. 
*/

--find the path for SQL Error Log
USE master
GO
xp_readerrorlog 0, 1, N'Logging SQL Server messages in file', NULL, NULL, NULL
GO

--sproc to view SQL Error Log.  Requires 4 parameters


--1: This parameter is to specify which error log to read from the 6 default logs (0 - 5)
--2: This parameter to query the SQL Server Error Log or the SQL Server Agent Error Log. 1 or null = SQL Server Error Log. 2 = SQL Server Error Log. 
--3: Specify word or phrase that we are looking within the text/message on the SQL Server error log or SQL Server Agent error log.
--4: Same as 3 

EXEC sp_readerrorlog NULL, NULL, NULL, NULL --<< 4 parameters
EXEC sp_readerrorlog 0, 1, NULL, NULL  --<< 1 = SQL Server Error Log
EXEC sp_readerrorlog 0, 2, NULL, NULL  --<< 2 = SQL Agent Error Log

EXEC sp_readerrorlog 0, 1, 'starting', 'master'

SSMS GUI method

Connect to a SQL instance in SSMS and navigate to Management. Expand the Management folder, followed by SQL Server Logs. In the SQL Server Logs, you can see different logs – Current and Archive:

SQL Server error logs

Double-click on the desired error log file and it will open the error log in a separate window:

View error log in SSMS

T-SQL method

You can use SERVERPROPERTY() command to know the current error log file location

SELECT SERVERPROPERTY(‘ErrorLogFileName’) AS ‘Error log file location’;
Error log location

You can browse to the directory and it shows available error log files:

  • ErrorLog: Current error log file
  • ErrorLog.1: Archive error log file 1
  • ErrorLog.2: Archive error log file 2
View error log in the log folder

Here is a sample SQL Server Agent Job Failure Notification.

job step failure

Here are the details of Log File Viewer for the Backup Job Failure.

job step failure in log viewer

As you can see, neither of these gives much information about why it failed.

Configuring the SQL Server Agent Job to Capture More Information

For the sake of this tip I’ll be using the SQL Server Management Studio (SSMS) GUI. Let’s jump into how to configure the job step.

SQL Server Agent Advanced tab

Open up the SQL Agent job you want to add verbose logging to and navigate to the Steps page. If you have multiple steps then edit the first step, in particular we are going to make changes on the Advanced tab of the step.

sql agent job step

Specify the Output File

Using the Output file field, enter an existing file path (I tend to use the log directory for SQL Server so as to remember where these log files are located). Append to the file path either an existing or non-existing file to use for the verbose logging. If the file does not exist one will be created for you the first time the job is run:

sql agent job step

Output File Contents

Based upon the screen shot above this is the contents of the DB_Backup_Log.txt file after I re-execute the job:

sql agent job output

SQL SERVER – Enable Additional Logging for SQL Server Agent

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.

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

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.