How to determines the server default instance name and which one is my default instance in MS SQL Server?

1.5K    Asked by MariaHodges in SQL Server , Asked on Aug 26, 2021

I have two instances in my server and I want to know which one of them is the default. Can you, please, point out ways I can recognize it. By saying a default instance I mean the one that listens for 1433. So how can I determine sql server default instance name?

 

Thanks for your support.

Answered by Puja Khatri

login into your server and run this

    SELECT SERVERPROPERTY('InstanceName')

for sql server 2008 and above if it returns null then it's a default instance else named one

InstanceName: Name of the instance to which the user is connected.

Returns NULL if the instance name is the default instance, if the input is not valid, or error.

NULL = Input is not valid, an error, or not applicable.

Base data type: nvarchar(128)

Ref

Another one using SEVERPROPERTY(),

Run the below code

    SELECT SERVERPROPERTY('ServerName') SELECT SERVERPROPERTY('MachineName')

if both returns same value ie machine then it's your default instance else the first one return with your machinenameinstance name

After your edit

if you want to check the SQL server service which listens to port 1433. Then you have to check that in SQL server configuration manager. Check-in SQL server network configuration in that protocols for SQL server and click properties for TCP/IP and see the port used under IP addresses

Or you can follow these steps you can follow to Identify the SQL Server instance name

  • Open a command prompt window.
  • Execute: services.msc.
  • Scroll down to entries beginning with SQL.
  • Locate an entry for each installed named SQL Server (instancename) . The value in parenthesis is the instance name.

This way you can find out sql server default instance name.



Your Answer

Interviews

Parent Categories