Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

Using TSQL, how can I find if the MS SQL server (any version) is configured to use static port or is it using dynamic port?

Script i am trying to use:

        ,@RegKey VARCHAR(100)

        SET @RegKey = 'SOFTWARE\Microsoft\Microsoft SQL Server\' + @@SERVICENAME + '\MSSQLServer\SuperSocketNetLib\Tcp\IPAll'

EXEC master..xp_regread
    @rootkey = 'HKEY_LOCAL_MACHINE'
    ,@key = @RegKey
    ,@value_name = 'TcpPort'
    ,@value = @TcpPort OUTPUT

EXEC master..xp_regread
    @rootkey = 'HKEY_LOCAL_MACHINE'
    ,@key = @RegKey
    ,@value_name = 'TcpDynamicPorts'
    ,@value = @TcpPort OUTPUT

SELECT @TcpPort AS PortNumber
        ,@@SERVERNAME AS ServerName
        ,@@SERVICENAME AS ServiceName
share|improve this question
For your @@SERVICENAME key, remove the \IPAll from the end of the string. – Mark Storey-Smith Oct 18 '11 at 14:27
Thanks heaps!!!! – Manjot Oct 25 '11 at 22:07
up vote 8 down vote accepted

You can determine which port an instance is using from the error log but not whether its static or dynamic.

EXEC xp_readerrorlog 1, 1, 'Server is listening'

Alternative would be to read from the registry via xp_regread.

    @StaticPort NVARCHAR(512)
    , @DynamicPort NVARCHAR(512)

EXEC xp_regread
    , 'SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLServer\SuperSocketNetLib\Tcp\IPAll'
    , 'TcpPort'
    , @StaticPort OUTPUT

EXEC xp_regread
    , 'SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLServer\SuperSocketNetLib\Tcp\IPAll'
    , 'TcpDynamicPorts'
    , @DynamicPort OUTPUT

    @StaticPort AS StaticPort
    , @DynamicPort AS DynamicPort
share|improve this answer
xp_regread: is it present in sql server 2008? – garik Oct 17 '11 at 8:43
Undocumented and unsupported but yes it is there. – Mark Storey-Smith Oct 17 '11 at 10:01
+1 Thanks for information. – garik Oct 17 '11 at 10:53
@MarkStorey-Smith I tried to generalize this for multiple SQL servers but I don't see anything. Did i miss something? Script has been appended to the question – Manjot Oct 17 '11 at 22:28
@Manjot, daft typo in my script above, now corrected. Prefer your version by the way, have filed it away in the script library. – Mark Storey-Smith Oct 18 '11 at 14:25

I think, it is possible (theoretically):

share|improve this answer
thanks..yes Use static, dynamic port allocation what and how to read from registry sounds appropriate... but how can I know which key to read? – Manjot Oct 17 '11 at 0:09
@Manjot see SQL Server xp_regread with T-SQL – garik Oct 17 '11 at 7:45

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.