Search This Blog

Wednesday, January 19

How to find the SQL server port number

One day i got a call from my junior DBA. She asked me, how can i find out the port number for a particular server? Then our conversation started,

Me:
I asked a question to her what version of SQL server is that.

Junior DBA:
Its SQL server 2005

Me:
I told her to check the configuration manager.

Start --> All programs --> Microsoft SQL server 2005 --> Configuration Tools --> SQL server configuration Manager.


Junior DBA:
I don’t have direct remote access. (MSTSC)

Me:
After that, I told to check the SQL error log by using T-SQL

SP_readerrorlog 0,1,'listening','server'


Junior DBA:
I din’t see ‘'listening' such a word.

(0 row(s) affected)

Me:
Again i told to change the first parameter 0 to 1.

sp_readerrorlog 1,1,'listening','server'

Junior DBA:
Yep, I got it.

Results:

2011-01-19 11:36:23.340        Server Server is listening on [ 'any' <ipv6> 1433].
2011-01-19 11:36:23.340        Server Server is listening on [ 'any' <ipv4> 1433].

Here is another one method,
(for default instance)

DECLARE @findport_number nvarchar(5)

EXEC xp_regread
@rootkey    =    'HKEY_LOCAL_MACHINE',
@key        =    'SOFTWARE\MICROSOFT\MSSQLSERVER\MSSQLSERVER\SUPERSOCKETNETLIB\TCP',
@value_name    =    'TcpPort',
@value        =    @findport_number OUTPUT

print 'The server port number = '+@findport_number