Identify TCP Port Number of a SQL Instance

There are a lot of instances when you are asked to identify TCP port number for a SQL instance unless you use default port (1433) across the board. There are several ways to achieve this and as a DBA you should always be looking at ways to get work done accurately with least possible effort.

Identify Port Number via SQL Server Error Logs

As per BOL “SQL Server error logs contains user-defined events and certain system events”. To me, SQL Server error logs, this holds great information on what is happening with your db server. Along with plethora of information, it also holds information about the tcp port number of the instance. A simple tsql will fetch the required information without you rdp’in to the server.

exec master..xp_readerrorlog 0,1,'Server is listening on'

Notes:

• If your environment uses sp_cycle_errorlog then the above query will yield “(0 row(s) affected)”. In this case you will need to search the archive logs. Logging of port number is only in startup error logs.
• If your environment uses SQL Database Mirroring you will see those ports as well. To avoid the confusion look under column ProcessInfo where value = ‘Server’.

Identify Port Number via DMV – sys.dm_exec_connections

There is an excellent article by Manohar Punna providing detailed insight on sys.dm_exec_connections. Again, this is an excellent way to fetch the port number without rdp’in to the server.

SELECT local_tcp_port as [SQLServer_PortNumber] FROM sys.dm_exec_connections
WHERE session_id = @@spid --this is your connection spid

with the same end result:  

Identify Port Number via  SQL Server Configuration Manager

This step will require you to rdp/connect to the server. Connecting to SQL Server Configuration Manager can be achieved  in 2 ways

1. Using run command (Start –> run or ) and type – sqlservermanagerXX.msc (XX –10 for 08/R2; 11 for 12)
2. Go to Start–>All Programs–> Microsoft SQL Server xxx–>Configuration Tools–>SQL Server Configuration Manager
   • Under SQL Server Configuration Manager–>SQL Server Network Configuration–>Select Protocols for <select instance>
   • Goto Properties for TCP/IP–>IP Addresses(tab)–>Scroll all the way to IPAll–>TCP Port

Identify Port Number via Windows Event Viewer

This step will require you to rdp/connect to the server. You can connect to Event Viewer

Go to Start–>All Programs–>Administrative Tools–> Event Viewer

1. In there click on Windows Logs–>Application and find via either ways
   • EventId – 26022
   • Description – Server is listening on [ ‘any’

Notes:

• If you are searching via EventId and if your instance has db mirroring enabled you will have to cycle till you see the description  “Server is listening on [ ‘any’“

There are additional methods floating around (powershell, xp_instance_regread) but my main focus – as a DBA – is to keep it simple without doing to many additional tasks.

~ Adios

Khan

Like us on FaceBook | Join the fastest growing SQL Server group on FaceBook |
Follow me on Twitter