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'

Capture

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:  dmv_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

sql_server_configuration_manager

IPAll


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

Data Platform Virtual Summit 2020

Subscribe to SQLServerGeeks YouTube channel. If you want more learning content in your inbox, subscribe to SQLServerGeeks Bulletin.

SQLServerGeeks YouTube | SQLServerGeeks Bulletin | SQLServerGeeks Twitter

Avatar

About Arsalan Khan

People know me as 'Khan' ... nope not Shah Rukh's movie but Star Trek II - Wrath of Khan and since than 'Khan' it has been. Born and raised in Dubai before moving to US for my masters. Been blessed with solid 8+ yrs with SQL (and counting) and currently working for the biggest publishing house as Sr. Database Administrator. I have had an opportunity to speak about tips & tricks to write efficient tsql for Quest International Users Group (PeopleSoft). Love playing TT, badminton and thoroughly enjoy watching cricket when not occupied with my loving daughter. Expertise in DR, Performance Tuning, Troubleshooting and Problem Solving. With that being said I have finally decided to roll my sleeves up and give back to the community bit by bit. Finally.. as my wonderful wife puts it ... "if you don't have a smile, I will give you one of mine ~ Rabia Khan" ~Cheers

View all posts by Arsalan Khan →

Leave a Reply

Your email address will not be published. Required fields are marked *