SQL SERVER – Unable to Start SQL Service – Server TCP provider failed to listen on [‘any’ 1433]. Tcp port is already in use.

While playing with my multiple SQL instances, I realized that I was not able to start one of the SQL instance on my laptop. Here is the error which I received when I tried starting it from Start > Run > Servics.msc . This error is related to TCP provider.

Windows could not start the SQL Server (MSSQLSERVER) on Local Computer. For more information, review the System Event Log. If this is a non-Microsoft service, contact the service vendor, and refer to service-specific error code 10048.

I opened ERRORLOG file and found below message.

2016-12-07 05:39:05.52 spid11s Server is listening on [ ‘any’ 51823].
2016-12-07 05:39:05.52 spid11s Error: 26023, Severity: 16, State: 1.
2016-12-07 05:39:05.52 spid11s Server TCP provider failed to listen on [ ‘any’ 51823]. Tcp port is already in use.

2016-12-07 05:39:05.52 spid11s Error: 17182, Severity: 16, State: 1.
2016-12-07 05:39:05.52 spid11s TDSSNIClient initialization failed with error 0x2740, status code 0xa. Reason: Unable to initialize the TCP/IP listener. Only one usage of each socket address (protocol/network address/port) is normally permitted.

2016-12-07 05:39:05.52 spid11s Error: 17120, Severity: 16, State: 1.
2016-12-07 05:39:05.52 spid11s SQL Server could not spawn FRunCommunicationsManager thread. Check the SQL Server error log and the Windows event logs for information about possible related problems.

Application event logs show below message.

Log Name: Application
Source: MSSQLSERVER
Date: 12/7/2016 5:38:18 AM
Event ID: 26023
Task Category: Server
Level: Error
Keywords: Classic
User: N/A
Computer: sqlserver2016
Description:
Server TCP provider failed to listen on [ ‘any’ 51823]. Tcp port is already in use.

SOLUTION/WORKAROUND

By looking at error messages above, we know that SQL is trying to start on post 51823 and someone else is already using that port. We have two choices at this point.

  1. Find the process which is using 51823 ports and stop that process.
  2. Change port of SQL Server to a port which is not used by any other process.

To find out details about port usage, I generally use a free tool called TCPView. This utility is a sysinternals tool which gives us the information we need to fix this issue. Just start it, pause the data view, and look at which process is using the local port with the TCP protocol. As we can see below PID 3724 is using “Local Port” 51823 which is there in error message.

SQL SERVER - Unable to Start SQL Service - Server TCP provider failed to listen on ['any' <ipv4> 1433]. Tcp port is already in use. port-use-01-800x213

Once we identified know the process that is already using that port, we can take the appropriate action.

If you are not allowed to think in above direction, then you can always change the port in which your SQL Server instance is listening on, but I’d personally be extremely curious as to which process is already using that port.

Configure a Server to Listen on a Specific TCP Port (SQL Server Configuration Manager)

Have you seen such behavior and how did you fix it? Please comment and let me know and share with other blog reader.

Reference: Pinal Dave (https://blog.sqlauthority.com)

, ,
Previous Post
SQL SERVER – Database Mirroring Login Attempt Failed with Error ‘Connection Handshake Failed’
Next Post
SQL SERVER – How to get historical deadlock Information from System Health Extended Events?

Related Posts

7 Comments. Leave new

  • Kevin Michael Fries
    January 15, 2017 12:41 am

    I found this to be a good resource on the topic, it includes some other commands and scripts besides tcpview.exe

    Reply
  • Good information..liked it

    Reply
  • Hi Pinal..
    I am facing same issue however when I go in windows I dont see any process running though that SPID (strange)
    When I try to reset port in configuration manager and restart service it fails with same error and resets port details in configuration manager itself .

    Reply
  • I found below messages : please help :

    2020-09-09 00:19:21.44 spid12s Server failed to listen on ‘any’ 49164. Error: 0x2739. To proceed, notify your system administrator.
    2020-09-09 00:19:21.44 spid12s Error: 17182, Severity: 16, State: 1.
    2020-09-09 00:19:21.44 spid12s TDSSNIClient initialization failed with error 0x2739, status code 0xa. Reason: Unable to initialize the TCP/IP listener. A protocol was specified in the socket function call that does not support the semantics of the socket type requested.
    2020-09-09 00:19:21.44 spid12s Error: 17182, Severity: 16, State: 1.
    2020-09-09 00:19:21.44 spid12s TDSSNIClient initialization failed with error 0x2739, status code 0x1. Reason: Initialization failed with an infrastructure error. Check for previous errors. A protocol was specified in the socket function call that does not support the semantics of the socket type requested.
    2020-09-09 00:19:21.44 spid12s Error: 17826, Severity: 18, State: 3.
    2020-09-09 00:19:21.44 spid12s 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.
    2020-09-09 00:19:21.44 spid12s Error: 17120, Severity: 16, State: 1.
    2020-09-09 00:19:21.44 spid12s SQL Server could not spawn FRunCommunicationsManager thread. Check the SQL Server error log and the Windows event logs for information about possible related problems.

    Reply
  • Lai Bhari….. solved my problem….. Thank You So Much….!

    Reply
  • I found the same error while restarting services. As a DBA should we contact network team to resolve the issue!!

    Reply

Leave a Reply

Menu