SQL Server Agent (5/12): Understanding SQL Agent Error Logs

SQL Server Agent is the core of all real-time databases. Proxies have a lot of non-obvious uses, so the knowledge of the system is useful for developers or DBAs. This series of articles will be popular to introduce its many uses.

As we saw in the previous articles in this series, SQL Server Agent consists of a series of job steps, each of which is going to work for different types. If you see in the 4th article, SQL Server Agent also provides the ability to send reminders using Database Mail. If there is a problem, anyway, you have to check the Database Mail error log. In this article, you will learn how to understand and view all the relevant knowledge of the SQL Server error log. You will see the most common error logs, as well as understanding how to know when a piece of information requires you to take action and what is the most direct information for you. Once you understand the error log, you will save a lot of time when you are troubleshooting SQL Server Agent-related problems.

SQL Server Agent error log

SQL Server Agent maintains its own error log, separated from the SQL Server error log. SQL Server error log location \mssql\log directory under the directory where SQL Server is located (C:\Program Files\Microsoft SQL Server\mssql10_50.mssqlserver\mssql\log , this article uses SQL Server 2008r2 as the default instance). The SQL Server error log defaults to errorlog (no file name extension), and the SQL Server Agent error log is named Sqlagentout by default. In this article, we spend most of our time in the SQL Agent error log, and you need to look at the SQL Server error log anyway.

To view the SQL Server Agent error log

When you first browse to the \mssql directory and try to view the log directory, it is likely that you need to ask for permission to elevate your permissions to access this directory. By default, SQL Server and SQL Server Agent service accounts have read and write access to this directory, but you cannot access it (using a user account). If you are a local administrator, you can authorize yourself to view the log files directly after you access them. As shown in 1, the default permissions and security settings are the same. Do not remove permissions from the directory for your service account, or SQL Server and SQL Server Agent will not work properly.

Illustration 1:sql server's log directory permissions

Once you are in the \mssql\log directory, you will see a lot of log files for SQL Server and SQL Server Agent, and the original log files (as long as you start the service normally, each time you create it) are also in the directory. SQLAGENT. The out file is the current log file for SQL Server Agent, so this is the file you want to open. By default, when you try to open this file, you need to choose to open this suffix for. The default program for out files. Notepad is available, and when you have another preference, you can choose the text Viewer for this extension file name (2, select Notepad).

Illustration 2:. The out file is opened with Notepad.

Once selected, SQLAGENT. The out file opens, as illustrated in Figure 3.

Illustration 3:sql Server Agent error log (sqlagent.out)

You can easily see that there should be a better way to look at the error log, yes! SSMs has a log file viewer, which has been briefly introduced in the 4th Database Mail. Switch to the error log, expand the SQL Server Agent folder, the error log folder, and you will see the current SQL Server Agent log as well as the most recent log. Double-click Current (or right-click to select "View Agent Log") and you will see SQL Server Log Viewer, where the current SQL Server Agent error log is checked (as illustrated in Figure 4).

Fig. 4: Log file Viewer for current SQL Server Agent log files

This is the same as the log file shown in illustration 3, but looks more intuitive and better managed. The first thing you'll notice is that the event is now divided into: information, warnings, and errors. Using the filter buttons (as illustrated in Figure 5), you can add your own filter criteria to limit the SQL Server Agent error log entries (or any SQL Server error logs that you have seen in the log file viewer, you can see all the logs)

Fig. 5: Filter settings for the log file viewer

Log file Contents

There is an informational message at the beginning of the SQL Server Agent log. We go through each step and briefly introduce the meaning of the message.

[393] Waiting for SQL Server recovery database ...

The SQL Server Agent service has started, but it cannot be configured and run because the msdb database is not yet available. Most of the configuration and settings for SQL Server Agent are saved in msdb. Some of the configuration details are saved in the System registration table. SQL Server cannot start until the msdb database is resumed. The number before the message (393) is the internal information number of the SQL Sever agent, and I have never seen the error number of a public document record SQL Agent.

[+] Microsoft SQLServerAgent version 10.50.1600.1 (build number x86 Unicode retail): Process ID 3412

This is a very useful piece of information. SQL Server Agent version number (that is, version, service Pack, and hotfix). In this example, it is SQL Server R2, which uses the SP1 update package. You can find the version number in Sqlservercentral.
The next thing is that this is a SQL Server x86 version (ie 32-bit), which is a standard retail version of the product. Finally, the Windows process ID of the SQL Server Agent service is identified. If you use Windows troubleshooting tools (even simple task Manager), you will see a process ID that will help you associate the SQL Server Agent service to Windows information.

Fig. 6: Task Manager with PID column display

[101] SQL Server PC201510181429 version 10.50.1600 (connection limit: 0)

This message identifies the SQL Server name (PC201510181429) and the SQL Server version number. Note the "0 connection limit"-that is, there is no specific configuration, so unlimited connections are allowed (until memory is exhausted).

[102] SQL Server ODBC driver version 10.50.1600
[103] The NetLib used by the driver is DBNETLIB. DLL; The local host server is

These two messages indicate the version number using ODBC (SQL Server Agent uses ODBC to connect back to the local copy of the SQL Server, in fact, it is connected with the local DBNETLIB.DLL.

[310] detection of 2 processors and 3327 MB RAM
[339] The local computer is PC201510181429 and is running Windows NT 6.1 (7601) Service Pack 1

The next two lines show the SQL Server CPU and memory configuration, as well as the Windows version.

[432] There are 12 subsystems in the subsystem cache

This is the second SQL Server Agent subsystem (SQL Server Agent job step and subsystem) discussed. There are various types of jobs under which you can run subsystems (such as CmdExec, ActiveX scripts) and some subsystems that support replication, Analysis Services, and you can view the subsystem inventory in the Msdb.dbo.syssubsystems table.

[364] Messenger service has not been started-netsend notification will not be sent

Although this is categorized as a bug, you can safely ignore it on most systems. Net send notification, which has been canceled from several versions of SQL Server, and the associated Windows service is not enabled by default. If you are still using the net send message and you see such an error, you need to change the Windows Messenger service to start automatically.

[129] SQLServerAgent started under Windows NT service Control

This message indicates that SQL Server Agent started as a service, not at a command prompt window.

[260] Unable to start mail session (Reason: Microsoft.SqlServer.Management.SqlIMail.Server.Common.BaseException: Unable to read message configuration information from the database.) ...... [355] message system initialization failed; Please check configuration settings

These messages indicate an error in the Database Mail configuration. Review the fourth Database Mail to see if there is a similar problem.

[396] idle CPU condition not defined-OnIdle job schedule will not play any role

This warning indicates that a SQL Server Agent setting is not configured and may prevent some jobs from running. You can set the job to run when the CPU is "idle" (mentioned in the first article). However, the idle definition in this system has not yet been set. In SSMs, configure this option, right-click SQL Server Agent, select Properties, and then click the Advanced tab. Configure the Idle CPU condition as illustrated in Figure 7

Fig. 7: Configuring Idle CPU Conditions

As you can imagine, there are many other errors in your SQL Server Agent error log and you should check the logs regularly. Keep in mind that the agent log is not used to troubleshoot job failures, and you need to review the history of each failed job step. We will do more troubleshooting in subsequent chapters.

Configuring the SQL Server Agent error log

You can configure some of the properties of the SQL Server Agent error log in SSMs, and other properties that require the use of Registry Editor (which is not supported, so it is not recommended in production systems).
Right-click the SQL Server Agent---error log in SSMS and you will see a set of options. First is "Configure", select this option and you will see something like Figure 5.8. You can change the location where the SQL Server Agent error log files are stored (not recommended), and you can change the agent log level as well. These settings cannot be visually resolved – if you tick "error", you will see errors and informational messages. If you tick "warning", you will see a warning and informational message. If you only tick the information, you do not see any error or warning messages in the error log.
Another option in the dialog box is to write to the OEM error log, which means to write a non-Unicode error log file. Theoretically you can save space through it, but it is also not recommended.

Illustration 8--Configuring SQL Server Agent

Exit Illustration 8, right-click the error log, and another option is recycle. This option closes the existing SQL Server Agent error log and starts a new log file, renaming the current Sqlagent.out file to Sqlagent.1 (all old file suffix numbers increment, such as sqlagent.1 into Sqlagent.2). This is useful if you want to send the current log file to SQL Server product support.
SQL Server Agent Some other configuration needs to be set in the registry (in fact, the settings for the actions above will change the corresponding key values in the registry). Remember that it is not recommended to modify the registry directly, so changing these settings is at your own risk. However, if you want to view the available settings (some SQL Server proxy settings can only be modified in the registry), you can view the available registry key settings under the SQL Server instance in the registry. For example, on my machine, the registry key is located in the HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL server\mssql10_50.mssqlserver\sqlserveragent , as illustrated in Figure 9

Illustration 9:sql registry key for Server Agent

If your SQL Server Agent configuration is really problematic, you may need to describe (or export) these settings to product support.

Next Trailer

The SQL Server Agent error log contains a lot of useful information (SQL Server Agent, SQL Server, and Windows Server). You can use the error log to troubleshoot errors in the SQL Server Agent service and to understand the information that is common in the logs. This article also describes how to troubleshoot some of the most common errors in SQL Server Agent.
With the previous SQL Server Agent basics, the sixth article will dig deeper into SQL Server Agent job steps and workflows.

SQL Server Agent (5/12): Understanding SQL Agent Error Logs