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 didn’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)
CREATE PROCEDURE usp_getport_number AS BEGIN 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 END EXEC usp_getport_number
