If someone tells me any situation, the first question which I always ask is “What is the error message?”. Now to get the right error message, it is important to look at the right log file. In this blog, we would learn about SQL Server Agent startup issue.
If SQL Server Agent service is not getting started, here are the logs I always look at:
- out file. This is generally located in the same folder where we have SQL Server ERRORLOG file. You can refer to below blog to find the location. SQL SERVER – Where is ERRORLOG? Various Ways to Find ERRORLOG Location
- If there is nothing interesting over there or the file itself is missing, then I would look at Event logs (both application and system). To open it, we can use the shortcut.
Start > Run > EventVwr.msc
For this client, here are the messages in various logs.
- In SQLAgent.out, we found below 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)
From above, we could see some ODBC errors but not exact cause of the issue. So, as a next step, I opened event viewer and found below message at the same time when SQL Agent startup was attempted.
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.
WORKAROUND/SOLUTION
After seeing above mentioned message in application event log, I was convinced that there is some issue with SQL Server Native Client component. To fix this, we went to add/remove program and performed a repair of “SQL Server Native Client” as shown below.
Once we repaired the component, SQL Server Agent was able to start.
Have you encountered such issues which are solved by messages in the event log?
Reference: Pinal Dave (https://blog.sqlauthority.com)
6 Comments. Leave new
This is exactly what we were looking for. Had spent lot of time around SQL. FInally got it. Thanks Pinal.
I tried to repair SQL Server Native Client according to what mentioned above but failed and it is said that the installer has insufficient privileges to modify the file. Please help. Thanks!
Looks like corruption in permission.
Try removing and adding again.
Hi Pinal,
I am getting this error when i run the SSIS package from Integration Services Catalog (Using SSMS 17).
Error: There was an error trying to establish an Open Database Connectivity(ODBC) connection with the database server. SqlState = IM004 Message = [Microsoft][ODBC Drive Manager] Driver’s SQLAllocHandle on SQL_HANDLE_ENV failed.
Can you please guide me what would be the cause for this issue?
Thanks,
AJ
This was really helpful! Thanks
Your posts always help me! I have having issues connecting to SQL Server from Tableau after installing 2019 with error “[Microsoft][ODBC Driver Manager] Driver’s SQLAllocHandle on SQL_HANDLE_ENV failed”
There were several posts giving solution but your post tells how to actually cofirm the driver is the problem. finding facts gives confidence in solution rather than guess work.
Thank you I was able to confirm issue from event log and fix it just by simple repair of ODBC 17 SQL Server driver.