Different ways to check the SQL Server Instance Port number
Posted by Jugal Shah on January 18, 2012
Problem: If there are multiple SQL instances running on the same computer, it is difficult to identify the instance port number. You can use the below solution to find the instance specific port numbers.
Solution: You can check the list of port number used by the SQL Server instances using one of the below way.
Soln 1# Using SQL Server Configuration Manager
- Go to SQL Server Configuration Manager
- Select Protocols for SQL2005/2008 under SQL server Network Configuration
- Right click on TCP/IP and select Properties
- Select the IP Addresses-tab
- In the section IP ALL, you can see the ports
Soln 2#From Registry Values
SQL Server 2005
Type the regedit command in Run window and check the below registry values.HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.#
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\ MSSQL.#\ MSSQLServer\ SuperSocketNetLib\TCP\IPAll
SQL Server 2008
Default instance
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQLServer\SuperSocketNetLib\TCP\IPAll
Named instance
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.(InstanceName)\MSSQLServer\SuperSocketNetLib\TCP\IPAll
Soln 3# Error Log
Query the error log as below to get the port number.
EXEC xp_readerrorlog 0,1,”Server is listening on”,Null
Soln 4# Command Prompts
Execute the below command from the command prompt.
Netstat -abn
gkabir said
very good tip – we appreciate it.
Harish said
Hi Shah,
EXEC xp_readerrorlog 0,1,”Server is listening on”,Null
This qurey dispalying result zero effected. i am running this query in sql server 2005.
Regards,
Harish
Jugal Shah said
Run the XP_ReadErrorLog and check whether “Server listening….” is exist in the current log file. If not increase the number…. execute sp_enumerrorlogs procedure to get the log count.