How to find port on which SQL Server is running?

How to find port on which SQL Server is running?

One of the basic question which is generally asked in interview is “How to find the port number on which SQL Server is listening?”.   

There are multiple ways to find the SQL server port number and they are listed below:

1.    Read SQL Server Error Log file

2.    You can see in SQL Server Configuration Manager

3.    You can see in Windows Application Event Viewer

4.    You can use the DMV to get the Port number

Read SQL Server Error Log file

One on the easiest way to find the port number of SQL server is "ERRORLOG". The SQL Server Error Log records information with respect to the port in which an instance of the SQL Engine is listening. We can execute the below T-SQL command to find this info:

TSQL-Command:

xp_readerrorlog 0, 1, N'Server is listening on'

Using SQL Server Configuration Manager

We can check the Port number in SQL Server Configuration Manager. We need to expand SQL Server Network Configuration  and then select Protocols for "SQL instance name" on the left side. To identify the TCP/IP Port used by the SQL Server Instance,  right click on TCP/IP and select Properties from the drop down as shown below.

Using Windows Application Event Viewer:

We can also check the port number in event viewer log.  Just open event viewer and select Application on the left side panel.  In the right panel you need to filter for events with Event-ID 26022 as shown in the below screen shot.

 To set a filter , right click on the Application and then select Filter Current Log.

Using the DMV to get the Port number:

We can use the DMVs   sys.dm_exec_connections DMV to identify the port number SQL Server Instance is listening on using below T-SQL code:

SELECT local_tcp_port

FROM   sys.dm_exec_connections WHERE  session_id = @@SPID

Thank You!