SQL SERVER – Find Port SQL Server is Listening – Port SQL Server is Running

Here is one of the very common interview questions I often ask people -

Q: What is the default Port SQL Server running on?

A: 1433 for TCP/IP and 1434 for USD connection.

I always get the correct answer for this question. However, when I ask a follow up question, so far I have yet not got a single correct answer.

Q: Great, now assumes that there is more than one instance of SQL Server running on the same server. What will be the port of the second SQL Server running on the server as the default port is already used by default instant?

A: (I have yet to get the correct answer for this one in interview).

Because of the same reason, I have decided to blog about this.

Here are different ways one can figure out on which port the second instance of SQL Server is running.

Method 1: using xp_readerrorlog

Execute following stored procedure on the instance where you want to find out port on which SQL Server is running.

USE MASTER
GO
xp_readerrorlog 0, 1, N'Server is listening on'
GO

The above query will return results something very similar to the following:

Method 2: Configuration Properties

Go to Windows >> Start >> SQL Server Program Folder >> SQL Server Configuration Manager

Now go to SQL Server Network Configurations >> Protocols for Your Server and go to TCP/IP and right click over it.

Now over here when you scroll down you will notice Port Details.

It is that easy to find the port of the second instance of SQL Server.

Reference: Pinal Dave (http://blog.sqlauthority.com)

About these ads

3 thoughts on “SQL SERVER – Find Port SQL Server is Listening – Port SQL Server is Running

  1. Slight correction. It’s not 100 % true that first instance takes default port of 1433. What you said is true If and only if the first instance is a default instance. If we happen to install a named instance first on server, SQL server dynamically assigns port # other than 1433 (shown in your example) which can be forced to listen on 1433, if it’s not being used by other SQL instance.

    Even if you install a named instance first and then install default instance, SQL server will still assign 1433 for the default instance irrespective when in sequence was it installed ( first or second ).

    So by default, default instance 1433 port # and named instance take port# other than 1433.

    ~ IM.

  2. It’s also possible to get the portnumber by reading the registry with my scirpt below:

    DECLARE @tcp_port nvarchar(5)

    if @@SERVICENAME = ‘MSSQLSERVER’
    BEGIN
    EXEC xp_regread
    @rootkey = ‘HKEY_LOCAL_MACHINE’,
    @key = ‘SOFTWARE\MICROSOFT\MSSQLSERVER\MSSQLSERVER\SUPERSOCKETNETLIB\TCP’,
    @value_name = ‘TcpPort’,
    @value = @tcp_port OUTPUT
    END
    ELSE
    BEGIN
    declare @keyname nvarchar(500)
    set @keyname = ” + ‘Software\Microsoft\Microsoft SQL Server\’ + @@SERVICENAME + N’\MSSQLServer\SuperSocketNetLib\TCP’

    EXEC xp_regread
    @rootkey = ‘HKEY_LOCAL_MACHINE’,
    @key = @keyname,
    @value_name = ‘TcpPort’,
    @value = @tcp_port OUTPUT

    END

    select @tcp_port

  3. You are right, you can get the port by using the method you mentioned, However In the above example the port shown is dynamic and not fixed, so whenever the server restart, it will change again. It would be good you should assign a fixed port to the instance, so that people across different networks can connect using the same fixed port.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s