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:
Double-click on the desired error log file and it will open the error log in a separate window:
You can use SERVERPROPERTY() command to know the current error log file location
|SELECT SERVERPROPERTY(‘ErrorLogFileName’) AS ‘Error log file 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
Here is a sample SQL Server Agent Job Failure Notification.
Here are the details of Log File Viewer for the Backup Job Failure.
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.
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:
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 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.
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.