As we’ve seen in the previous levels of this Stairway, SQL Server Agent jobs are made up of a series of job steps, with each step being a distinct type of work to be performed. As you saw in Level 4, SQL Server Agent also provides the capability of sending notifications with Database Mail. If something didn’t work correctly, however, you had to review the Database Mail error logs. In this level, you will learn how to understand and review all of the related SQL Server Agent error logs. You will review the most common error messages and understand how to know when a message requires you to take action, and which are simply information for you. Once you understand the error logs, you will save a significant amount of time when troubleshooting problems related to SQL Server Agent.

The SQL Server Agent Error Logs

SQL Server Agent maintains its own error log separate from the SQL Server Error Log. The SQL Server error logs are located in the \MSSQL\Log directory under the parent directory hosting SQL Server (C:\program files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\ on the default instance of SQL Server 2008 R2 used to produce this article.) The SQL Server error log is named ERRORLOG (with no file extension) by default, and the SQL Server Agent error log is named SQLAGENT.OUT by default. For this article we’ll spend most of our time reviewing the SQL Agent error log, however you may have need to refer to the SQL Server error logs as well.

Reviewing the SQL Server Agent Error Logs

When you first browse to the \MSSQL\ directory above and try to view the Log directory, you will likely get a request to elevate your privileges and get permission to the directory. By default, the service accounts for SQL Server and SQL Server Agent will have read/write privileges to the directory, but you (using a user account) won’t have access. If you are a local administrator, you can grant yourself access and then view the log files directly. An example of the permissions, showing the security settings that exist by default, is shown in Figure 1. Do not remove the permissions for your service accounts from this directory or SQL Server and/or SQL Server Agent may not function correctly.

Figure 1: SQL Server Log Directory Permissions

Once you’re in the \MSSQL\LOG directory, you will see several log files for both SQL Server and SQL Server Agent. By default, previous log files (created each time you start either service, as appropriate) are kept in the directory. The SQLAGENT.OUT file is the current log file for SQL Server agent, so that’s the file you’ll want to open. By default, however, when you try to open the file you’ll get a request to associate a program with the file extension .OUT. Notepad is usually good enough to view the file, but if you have another preference, choose your preferred text file viewer to associate with the file extension (selecting notepad is shown in Figure 2).

Figure 2: Associating Notepad with the .OUT file type

Once that’s selected, the SQLAGENT.OUT file will open, and an example is shown in Figure 3.

Figure 3: The SQL Server Agent error log (SQLAGENT.OUT)

As you can readily see, there has got to be a better way to review the error logs, and you’re right! SQL Server Management Studio (SSMS) has a log file viewer, which we briefly reviewed in the Level 4 on Database Mail. To navigate to the error logs, expand the SQL Server Agent folder, then the Error Logs folder, and you will see a list of the current SQL Server Agent error log and the last several logs as well. Double-click on the Current log (or right click and select “View Agent Log”) and you will see the SQL Server Log File Viewer, with the current SQL Server Agent error log selected in the viewer (as shown in Figure 4).

Figure 4: The Log File Viewer for the current SQL Server Agent log file

This is the same log file you viewed in Figure 3 with notepad, but now much more organized and manageable. One of the first things you may notice is that events are now categorized into informational, warning, and error categories. Using the Filter button (Figure 5), you can add your own criteria to limit the view of items in the SQL Server Agent error log (or any error log in SQL Server, as you’ve probably noticed the logs are all viewable in the Log File Viewer).

Figure 5 – Filter settings for the Log File Viewer

Log File Contents

The informational messages are usually present at the beginning of the SQL Server Agent log. Let’s step through each one and briefly review the meaning of the message.

[393] Waiting for SQL Server to recover databases…

This means the SQL Server Agent service was started, but is not yet able to configure and run itself because the msdb database isn’t available yet. The majority of configuration and settings for SQL Server Agent are stored in msdb. A few of the settings are stored in the Windows system registry. SQL Server Agent will be unable to proceed until msdb is recovered. The number at the beginning of the message is the message number from within SQL Server Agent. I’ve never seen a publicly documented list of SQL Server Agent error numbers.

[100] Microsoft SQLServerAgent version 10.50.1720.0 (x86 Unicode retail build) : Process ID 308

This is a very useful bit of information. The build number (i.e. the version, service pack, and hotfix level) applied to SQL Server Agent is identified. In this example, it’s SQL Server 2008 R2, with Cumulative Update Package 2 applied. You can find a useful list of build numbers on SQL Server Central.

The next thing here is that this is an x86 version of SQL Server (i.e. 32-bit), and it’s a standard retail build of the product. Finally, the windows process ID of the SQL Server Agent service is identified. If you are using windows troubleshooting tools (even simple ones like task manager), you will frequently see a process ID, and this will help you tie windows information about to the SQL Server Agent service. Note in Figure 6 that the SQLAGENT.EXE PID is 308, as shown in the error log.

Note: To get the view as shown in Figure 6, click the option to “Show processes from all users,” as well as selecting View -> Select Columns on the menu and check the “PID (Process Identifier)” option.

Figure 6 – Task Manager with the PID Column shown

[101] SQL Server RW2008R2 version 10.50.1720.0 (0 connection limit)

This message identifies the name of the SQL Server machine (RW2008R2), as well as the version number of SQL Server. Note the “0 connection limit” – that means there is no specific configuration and therefore an unlimited number of connections are allowed (until you run out of memory, that is).

[102] SQL Server ODBC driver version 10.50.1720

[103] NetLib being used by driver is DBNETLIB.DLL; Local host server is

These two messages show the version number of ODBC used (SQL Server Agent uses ODBC to connect back to the local copy of SQL Server, as well as the fact that it’s using the local DBNETLIB.DLL for the connection.

[310] 1 processor(s) and 1180 MB RAM detected

[339] Local computer is RW2008R2 running Windows NT 6.1 (7601) Service Pack 1

These next two lines show the CPU and memory configuration of the SQL Server machine, as well as the version of Windows (Windows 7 in this case).

[432] There are 12 subsystems in the subsystems cache

These are the SQL Server Agent subsystems we talked about in Level 2, SQL Server Agent Job Steps and Subsystems. There are subsystems for each type of job you can run (such as CmdExec, ActiveScripting, etc) as well as a number of subsystems to support replication, Analysis Services, etc. You can view the list of subsystems in the msdb.dbo.syssubsystems table.

[364] The Messenger service has not been started – NetSend notifications will not be sent

Although this is categorized as an error, you can safely ignore this on the vast majority of systems. NET SEND notifications have been deprecated for several releases of SQL Server, and the supporting Windows service isn’t on by default anymore. If you still use NET SEND messages and you see this error, you know that you need to change the Windows Messenger service to start automatically.

[129] SQLSERVERAGENT starting under Windows NT service control

This message indicates that SQL Server Agent was started as a service, rather than in a command prompt window.

[260] Unable to start mail session (reason: Microsoft.SQLServer.Management.SQLMail.Server.Common.BaseException….

[355] The mail system failed to initialize; check configuration settings

These messages indicate I made a mistake in my Database Mail configuration. Refer back to Level 4 on Database Mail if you are having similar issues.

[396] An idle CPU condition has not been defined – OnIdle job schedules will have no effect

This warning indicates that a setting for SQL Server Agent hasn’t been configured that may prevent certain jobs from running. You can set jobs to run when the CPU is in an “idle” state (as mentioned in the Level 1 in this Stairway). However, on this system the definition of Idle hasn’t been set. To configure this option in SSMS, right-click on SQL Server Agent, select Properties, then the Advanced Tab. Configure when you want “idle” jobs to run as shown in Figure 7.

Figure 7: Configuring the Idle CPU Condition

As you can imagine, there are many other error conditions that might appear in your SQL Server Agent error log, and you should periodically review the log to ensure you address any other failure conditions you might find. Do keep in mind, however, that this is not the log location to troubleshoot failures in an individual job, you will do that in the job history for each job step that failed. We will do more of that kind of troubleshooting in article later level.

Configuring the SQL Server Agent Error Logs

You can configure some of the SQL Server Agent error log properties in SSMS, and for some of the properties you will need to use the registry editor (which is unsupported, so not recommended on production systems).

Right-click on the Error Logs folder in SSMS under SQL Server Agent, and you will see a couple of options. The first is to “Configure” SQL Server Agent error logs. Select that option and you will see something like Figure 8. You can change the location of the SQL Server Agent error log files (not recommended), and also change the logging level of the events in the SQL Server Agent log file. The settings for these are not intuitive – if you have “Errors” checked, you will see errors and informational messages. If you check “Warnings”, you will see warnings and informational messages. If you check just Information, you won’t see any errors or warnings in your error log.

There is one other option in the dialog, and that’s to write an “OEM” error log. What this means is to write a non-unicode error log file. Theoretically you could do this to save space in your log file, but that’s also not recommended.

Figure 8 – Configuring the SQL Server Agent Error Logs

If you go back to the menu in SSMS (cancel out of the dialog in Figure 8), the next option for the SQL Server Agent Error Logs is “Recycle.” This option closes out the existing SQL Server Agent error log and starts a new log file, renaming the current SQLAGENT.OUT file to SQLAGENT.1 (and incrementing the number for all your older log files as well, i.e. SQLAGENT.1 becomes SQLAGENT.2, etc.). This is useful if you need to send a copy of your current log to SQL Server product support, for example.

There are a number of other settings for SQL Server Agent that are configured in the registry (in fact, several of these settings are also registry keys and the changes you make above change the registry for you). Keep in mind that registry editing is not directly supported, so change these settings at your own risk. However, if you want to review the available settings (and for some SQL Server Agent settings the ONLY way to change them is in the registry), you can view the available registry key settings under your instance of SQL Server in the registry. For example, for this instance of SQL Server Agent, the registry keys are located at HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\SQLServerAgent, as shown in Figure 9.

Figure 9: The registry for SQL Server Agent.

You may need to describe (or export) these settings for review with product support if you are having real trouble with your SQL Server Agent configuration.

What’s Next

The SQL Server Agent Error Logs contain much useful information about your SQL Server Agent, SQL Server, and Windows Server. You can troubleshoot errors with the SQL Server Agent service using the error log, and also understand common messages that appear in your log so you know what is safe to ignore when things aren’t running smoothly. This article also showed you how to address a couple of the most common error conditions when reported in the log for SQL Server Agent.

With the basics of SQL Server Agent now covered, Level 6 will dig much deeper in to job steps and workflow with SQL Server Agent.