Recently I came across an issue where SQL Server Agent Services had issues and failing to start. where SQL Server service started quickly, but had issues starting SQL Server Agent service and had to perform extensive troubleshooting to fix the issue. Documenting here so that it can be helpful to others facing similar issue. This is standalone SQL Server 2012 Named Instance on Windows Server 2012.
Below are some of the troubleshooting steps followed to resolve the issue.
– After reboot of the server, SQL Server services failed to start.
– Checked SQL Errorlog and found issues with port, as it was used by some other process and SQL Server could not use that port.
– We identified the Application using the SQL Server’s port and stopped that application after which we were able to start the SQL Server services and connect to SQL instance from SSMS as well.
– We noticed that SQL Server Agent was showing as stopped.
– Tried to start the SQL Agent service from SQL Server Configuration Manager, but it failed with below error.
The request failed or the service did not respond in a timely fashion. Consult the event log or other applicable errorlogs for more details.
– From SSMS, it was showing red cross mark .
– Checked SQL Agent Errorlog and found below errors
The SQL Server Agent startup service account is NT Service\SQLAgent$TestInstance.
SQL Server does not accept the connection (error: 64). Waiting for Sql Server to allow connections. Operation attempted was: Verify Connection On Start.
Unable to connect to server 'ServerNameHere\InstanceNameHere'; SQLServerAgent cannot start
SQLServer Error: 26, Client unable to establish connection because an error was encountered during handshakes before login. Common causes include client attempting to connect to an unsupported version of SQL Server, server too busy to accept new connections or a resource limitation (memory or maximum allowed connections) on the server. [SQLSTATE 08001]
SQLServer Error: 64, TCP Provider: The specified network name is no longer available. [SQLSTATE 08001]
SQLServer Error: 26, Client unable to establish connection [SQLSTATE 08001]
SQLServer Error: 64, Client unable to establish connection due to prelogin failure [SQLSTATE 08001]
Logon to server 'TestServer\TestInstance' failed (DisableAgentXPs)
SQLServerAgent terminated (normally)
– As per the last error message “DisableAgentXPs”, tried to enabled AgentXPs from sp_configure and tried to SQL Agent service, but still had same issue and same errors in SQL errorlog.
– Verified and made sure that SQL Server Agent service account was part of SQL Server LOGINS and was a SysAdmin as well and it is enabled and not locked out.
– I was able to connect to SQL instance locally using SSMS.
– Verified and MSDB database is ONLINE and I can query the tables.
– Tried connecting to SQL Server instance from SSMS on remote server, but it failed with below error
Cannot conntect to TestServer\TestInstance.
A connection was successfully established with the server, but then an error occurred during the login process. (provider: TCP Provider, error: 0 - The specified network name is no longer available.) (Microsoft SQL Server, Error: 64)
– Using telnet made sure SQL Server port is opened from remote server to SQL Server.
– No Antivirus was installed on the server.
– Verified and SQL Browser service is in running state.
– Verified TCP/IP, Shared Memory and Named Pipes protocols are enabled.
– Verified and there are no Aliases configured.
– Verified and reentered the password for the SQL Agent service account from SQL Server Configuration Manager and tried to start the service, but still no luck.
– Also, tried to start the SQL Agent service from services console (services.msc), but still same issue.
The SQL Server Agent service on Local Computer started and then stopped.
– No direction from SQL Server Errorlogs or Application/System Eventlogs.
– Finally we decided to run Repair of SQL Server instance.
– Performed Backup of System and User databases. Made a note of SQL Server version, Edition and build number which is already installed. Made a note of server level Collation being currently set.
– Ran the Repair of the SQL Server instance and shared components, which completed successfully and also fixed the issue.
– Now we were able to connect to the SQL Server instance and SQL Server Agent from SSMS from local server as well as remote servers.
– Tested the Application and there were no issues.
This is applicable on below versions of SQL Server
SQL Server 2005
SQL Server 2008 R2
SQL Server 2012
SQL Server 2014
Hope this was helpful.
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, SQL Server Frequently asked questions, SQL Server Trainings.