SQL Server Reports TCP Port Is Already In Use

PowerShell - SeniorDBA

If you ever have to restart you SQL Server instance and get an error message about the port already in use, then you will want to read this article by 

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

1
2
3
4
5
6
7
8
9
10
11
12
13
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.

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s