Fifth SQL Server Agent Understanding Agent error Log

This article is the fifth of the SQL Server Agent series, please refer to the original text for more information.

As mentioned in the previous series, SQL Server Agent jobs are composed of a series of job steps, each of which is executed by a separate type. In the fourth chapter we see that SQL Server Agent can send notifications through Database Mail. If anything is not correct, you must check the Database Mail log. In this article, you will learn how to understand and view all the relevant SQL Server Agent error logs. You will review the most common error messages, what information you need to take action, and what information is purely informational. Once you understand the error log, you will save a lot of time when dealing with SQL Server Agent issues.
SQL Server Agent error log
SQL Server Agent maintains its own errorlog (separate from the database errorlog). The database errorlog is located under the Xx\mssql\log directory. The database errorlog is named Errorlog (without a file extension) by default, and the database proxy errorlog is named Sqlagent.out by default. This article will take most of the time to understand the database proxy errorlog, but you also need to understand the database errorlog.
Review SQL Server Agent error logs
When you first browse to the \mssql\ directory mentioned above and then try to view the log directory, you may get a request to elevate your privileges to obtain the permissions of the corresponding directory. By default, the SQL Server and SQL Server Agent service accounts have read/write directory permissions, but you (using a user account) do not have permissions. If you are a local administrator, you can empower yourself and then view the log files directly. An example of a permission, shown in 5.1, shows the security settings that are present by default. Do not remove the service account's permissions to this directory, or SQL Server and/or SQL Server Agent may not function correctly.

Figure 5.1 SQL Server log Path properties
Once you are in the \mssql\log directory, you will see the SQL Server and SQL Server Agent log files. By default, the previous log file (created each time the service is started) is also saved in the directory. SQLAGENT. The out file is the current SQL Server Agent log file, so this file is what you want to open. By default, when you try to open this file, you will be asked to select the program you want to use to open the file. Usually Notepad is enough to view such files, but if you have a better one, choose your favorite text file viewer associated with the file (choose Notepad, shown in 5.2).
Figure 5.2 The associated notebook opens. Out file
Once selected, SQLAGENT. The out file will open, as shown in 5.3
Figure 5.3 SQL Server Agent error log
You can easily see that there should be a better way to see the errorlog. SSMs has a log file viewer, which we have simply used in Database Mail in the fourth article. Expand the SQL Server Agent folder, and then the error log folder, you see a list that contains the current SQL Server Agent error log and the last few error logs. Double-click the current log (or right-click and select "View Agent Log") and you will see the SQL Server log file Viewer, the current SQL Server Agent error log (5.4).
Figure 5.4 Log File Viewer view current Agent error log
This is the same log file with Notepad in Figure 5.3, but there is more organization and management. The first thing you notice is that the event is divided into informational, warning, and error categories. Using the filter button (Figure 5.5), you can add your own criteria to limit the SQL Server Agent error log view (you may notice that the log File Viewer can view the error log in any SQL Server).

Figure 5.5 Log File Viewer filter settings
Log file Contents
Informational messages, usually at the beginning of the SQL Server Agent log. Let's simply look at the meaning of the information below.
[393] Waiting for SQL Server recovery database ...
This means that the SQL Server Agent service starts, but has not yet been able to configure and run because the msdb database is not yet available. The primary configuration settings for SQL Server Agent are stored in the msdb library. A small number of settings are saved in the Windows system registry. SQL Server Agent cannot be used until msdb is restored. The number in front of the message is the error number inside the SQL Server Agent. I've never seen a public document record the SQL Agent error number.
[+] Microsoft SQLServerAgent version 10.50.1600.1 (build number x86 Unicode retail): Process ID 5280
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 to put Windows information to the SQL Server Agent service. In Figure 5.6
Figure 5.6 The process in Task Manager
[101] SQL Server USER-67NP5R8LGK\SQL08R2 version 10.50.1600 (connection limit: 0)
This message identifies the SQL Server name (USER-67NP5R8LGK\SQL08R2) 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 USER-67NP5R8LGK\SQL08R2
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 4 processors and 3018 MB RAM
[339] The local computer is USER-67NP5R8LGK 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] sqlagent$sql08r2 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: no mail profile defined)
[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 idle CPU condition as shown in 5.7
Figure 5.7 Setting the Idle CPU condition
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.
Figure 5.8 Configuring the SQL Server Agent error log
Exit Figure 5.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 , 9 shows
Figure 5.9 SQL Server Agent registry key
If your SQL Server Agent configuration is really problematic, you may need to describe (or export) these settings to product support.
Next Article
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 fundamentals, the sixth article will dig deeper into SQL Server Agent steps and workflows.

Fifth SQL Server Agent Understanding Agent error Log