Restarting the SQL Service may fail occasionally with the error, “TCP port is already in use”

Today, I was working with one of my clients with an issue where the application fails to connect to SQL server. The issue was encountered with the restart of the SQL services. The initial connections go through but within a very short duration the connections starts to fail. Thus when we tried to change the port number, the issue was mitigated for just a small duration and again the issue resurfaced.

From the error logs we see the below information :

2018-07-17 01:31:39.78 spid19s Server is listening on [ xx.xx.xx.xx <ipv4> xxxxx].
2018-07-17 01:31:39.78 spid19s Started listening on virtual network name 'xxx'. No user action is required.
2018-07-17 01:31:39.79 spid19s Error: 26049, Severity: 16, State: 1.
2018-07-17 01:31:39.79 spid19s Server local connection provider failed to listen on [ \\.\pipe\SQLLocal\SQL2 ]. Error: 0x50
2018-07-17 01:31:39.80 spid20s A new instance of the full-text filter daemon host process has been successfully started.
2018-07-17 01:31:39.81 spid19s Error: 17182, Severity: 16, State: 1.
2018-07-17 01:31:39.81 spid19s TDSSNIClient initialization failed with error 0x50, status code 0x40. Reason: Unable to initialize the Shared Memory listener. The file exists.
2018-07-17 01:31:39.81 spid19s Error: 17182, Severity: 16, State: 1.
2018-07-17 01:31:39.81 spid19s TDSSNIClient initialization failed with error 0x50, status code 0x1. Reason: Initialization failed with an infrastructure error. Check for previous errors. The file exists.
2018-07-17 01:31:39.81 spid19s Error: 17826, Severity: 18, State: 3.
2018-07-17 01:31:39.81 spid19s Could not start the network library because of an internal error in the network library. To determine the cause, review the errors immediately preceding this one in the error log.
2018-07-17 01:31:39.81 spid19s Error: 17120, Severity: 16, State: 1.
2018-07-17 01:31:39.81 spid19s SQL Server could not spawn FRunCommunicationsManager thread. Check the SQL Server error log and the Windows event logs for information about possible related problems.

On probing them, we found that they had updated their windows nodes with the KB 4338823 and on research we found that it is been documented as a known issue as per the article : https://support.microsoft.com/en-in/help/4338823/windows-7-update-kb4338823

This known issue has been resolved in the KB4345459 and once updating the nodes with this KB the issue is found to be resolved.

This issue might well be caused in case of the listening named-pipes not closed properly during the last shutdown of SQL Server. Due to which there will be orphan named-pipe handles in the windows kernel file system. Since the listening pipes are opened ACLing to the current user, if you happen to switch SQL Server to run under different account, you will get error 0x05(ERROR_ACCESS_DENIED). One example of this kind of scenario is when SQL Server is shutdown by kill process. In these situations, the only fix is to either switch back to previous account and make a clean shutdown of SQL Server, or reboot the machine. In most cases, I feel the latter is faster.

Hope this helps!! Happy connecting!!

Comments

• Anonymous
  July 23, 2018
  This article provided me the timely assistance needed during on of our outages. Thank you!!
  • Anonymous
    July 23, 2018
    Thanks for your feedback @sqllearnerinme
• Anonymous
  July 23, 2018
  Thank you Raghavendra for the update. To be sure of the issue, the following errors can also be found in the error logs which verifies the fact that the port is not in use. 2018-07-23 06:16:09.20 spid15s Error: 26023, Severity: 16, State: 1.2018-07-23 06:16:09.20 spid15s Server TCP provider failed to listen on [ 'any' 1433]. Tcp port is already in use.