Tag Archives: Unable to Connect

How to monitor SQL Server Services of multiple instances?

Problem:
I have a need to monitor the SQL Server Services in my environment. Are there any programmatic options to do so? Check out this tip to learn more.

Solution:
http://www.mssqltips.com/sqlservertip/2867/steps-to-monitor-the-sql-server-services/

Different ways to check the SQL Server Instance Port number

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