TCP Port Is Already In Use

  • Article

It's 3 AM on a Saturday morning and you receive that dreaded phone call – "The SQL Server just had maintenance. We rebooted and now SQL won't start"! You dig into the error logs and see something like this:

    2016-07-12 22:41:20.58 spidxxs Server TCP provider failed to listen on [ 'any' <ipv6> 1433]. Tcp port is already in use.
    2016-07-12 22:41:20.58 spidxxs Error: 17182, Severity: 16, State: 1.

You know there were no services using your port before the maintenance, so what's going on here and how do you find out who is using SQL's port?

There are a two situations that will cause this error message. First, the most obvious situation, is another Service or Application running on the server that uses the same TCP port as the SQL Server. In the case above, that would be tcp/1433. You can quickly check which process is using the port by running

  1. tcpview.exe from SysInternals
  2. PowerShell scripts
  3. Netstat

Examples of each method are listed below. To repro this scenario, I stopped SQL Server and ran a PowerShell script through PowerShell_ISE that listens on tcp/1433.

PowerShell

 
cls
$tcpPort = "1433"
$tcpConn = Get-NetTCPConnection | Where-Object {$_.LocalPort -eq $tcpPort -or $_.RemotePort -eq $tcpPort}
$tcpConn
$process = $tcpConn | Select-Object OwningProcess -Unique
if ($process -ne $null)
{
    Get-Process | Where-Object {$_.id -eq $process.OwningProcess} | Select-Object Id, ProcessName
}
else
{
    write-output "No services found using that port"
}

NetStat

netstat -p tcp -o -a -n

TCPView

The good news is that this situation is pretty simple to address – identify the owner of this application and then prevent it from running (kill it/stop the service/etc.) or change the port it listens on. There are other ways to handle this situation but none I would recommend at 3 AM on a Saturday.

The second situation involves a client running on the server using the same dynamic port as SQL Server. Every TCP session requires two endpoints – a client endpoint and a server endpoint. Each endpoint is an IP Address/Port combination - also known as a TCP socket. Normally, the client socket code will ask the OS to provide a port from a range of dynamic ports. The server socket is the IP/TCP port of the service the client is connecting to (SQL Server for example). To illustrate this, see the image below. The client port for ssms.exe is 11559 and 11560 (two active TCP sessions). The Remote Port or the SQL Server port is 1433.

So, how do you gain visibility into the dynamic port range that the OS uses? Simply run netsh int ipv4 show dynamicportrange tcp at a Windows command prompt.

Here is the output from my Windows 10 machine

What this means is that a client that requests a client port from the OS will get a free port in the range 1025 through 65534. As you see 1433 is in that range, so if you happen to have a client (think Windows OS system services) that start BEFORE SQL Server does, it could use TCP/1433 for its client socket. I've personally had this happen on several occasions. The biggest challenge here is that if it involves a System service, you cannot just stop the service, start SQL and continue on your merry way. You have to reboot and hope the situation does not happen post reboot. I've also had this happen through three reboots of the same machine L.

So, how do you fix and prevent a situation like this? There's another feature within the OS that allows you to tell the OS the TCP ports in the Dynamic Port Range that should be EXCLUDED. Once again, hit a Windows command prompt and execute netsh int ipv4 show excludedportrange tcp. Here is the output from my Windows 10 machine

You see there are already some port ranges that are excluded. These are system services that have exclusions by default – for example, 47001 is WinRM. All that is needed is to run the following with an Administrative Command Prompt netsh int ipv4 add excludedportrange tcp startport=1433 numberofports=1 store=persistent. After running this command, you will see the following excluded ports

After that setting is made, the OS will not give any clients that port. Services can listen on it still, but you prevent clients from getting it when asking the OS for a dynamic port. I would highly recommend you incorporate this into your server load process to safeguard against this issue.

I hope this helps.