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'
- 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
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
- Using run command (Start –> run or ) and type – sqlservermanagerXX.msc (XX –10 for 08/R2; 11 for 12)
- 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
- In there click on Windows Logs–>Application and find via either ways
- EventId – 26022
- Description – Server is listening on [ ‘any’
- 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.