When encountering issues with SQL Server Agent not starting, it is crucial to identify the root cause by examining the error messages. In this blog post, we will explore the various log files that can provide valuable insights into the problem.

1. SQL Server ERRORLOG

The first log file to check is the SQL Server ERRORLOG. This file is typically located in the same folder as the SQL Server installation. If you are unsure of the location, you can refer to our previous blog post on finding the ERRORLOG file.

Open the ERRORLOG file and look for any relevant error messages related to the SQL Server Agent startup. These messages can provide valuable clues about the underlying issue.

2. Event Viewer

If the SQL Server Agent ERRORLOG does not provide any useful information or is missing, the next step is to examine the Event Viewer logs. The Event Viewer contains both application and system logs that can shed light on the problem.

To open the Event Viewer, follow these steps:

1. Click on the “Start” button.
2. Go to “Run”.
3. Type “EventVwr.msc” and press Enter.

In the Event Viewer, look for any error messages related to the SQL Server Agent startup. These messages may provide additional details about the issue.

Real-Life Example

Let’s consider a real-life example to illustrate the troubleshooting process. In the SQLAgent.out file, we found the following error messages:

2018-01-28 12:01:34 – ! [165] ODBC Error: 0, Driver’s SQLAllocHandle on SQL_HANDLE_ENV failed [SQLSTATE IM004]
2018-01-28 12:01:34 – ! [382] Logon to server ‘(local)’ failed (ConnConnectAndExecuteSQL)
2018-01-28 12:01:34 – ! [165] ODBC Error: 0, Driver’s SQLAllocHandle on SQL_HANDLE_ENV failed [SQLSTATE IM004]
2018-01-28 12:01:34 – ! [382] Logon to server ‘(local)’ failed (SendCryptoKeyToServer)
2018-01-28 12:01:34 – ! [442] ConnConnectAndSetCryptoForXpstar failed (0).
2018-01-28 12:01:34 – ! [165] ODBC Error: 0, Driver’s SQLAllocHandle on SQL_HANDLE_ENV failed [SQLSTATE IM004]
2018-01-28 12:01:34 – ! [382] Logon to server ‘(local)’ failed (DisableAgentXPs)
2018-01-28 12:01:34 – ? [098] SQLServerAgent terminated (normally)

Although these messages indicate ODBC errors, they do not provide the exact cause of the issue. To further investigate, we checked the application event log in the Event Viewer and found the following message:

Log Name: Application
Source: SQLNCLI11.1
Event ID: 1
Task Category: None
Level: Error
Keywords: Classic
User: N/A
Computer: DBSRV1.SQLAuthority.com
Description: SQL Server Native Client 11.0: Unable to load sqlnclir11. rll due to either missing file or version mismatch. The application cannot continue.

Based on this message, we concluded that there was an issue with the SQL Server Native Client component. To resolve this, we performed a repair of the “SQL Server Native Client” through the add/remove program feature.

After repairing the component, the SQL Server Agent was able to start successfully.

Conclusion

When troubleshooting SQL Server Agent startup issues, it is essential to examine the appropriate log files. The SQL Server ERRORLOG and Event Viewer logs can provide valuable information about the underlying problem. By analyzing the error messages, you can identify the root cause and take the necessary steps to resolve the issue.

Have you encountered similar issues that were resolved by examining the event logs? Share your experiences in the comments below!