Skip to content

Instantly share code, notes, and snippets.

@EitanBlumin
Last active May 7, 2018 08:21
Show Gist options
  • Save EitanBlumin/fbb671790b4e4a2bc1afdcb3908389c3 to your computer and use it in GitHub Desktop.
Save EitanBlumin/fbb671790b4e4a2bc1afdcb3908389c3 to your computer and use it in GitHub Desktop.
Find SQL Server Instance TCP Port In Use
USE master
GO
-- Using SQL Error Logs:
xp_readerrorlog 0, 1, N'Server is listening on', N'any', NULL, NULL, N'asc'
-- will also return records for DB Mirroring endpoints
-- also, this won't work if error log was cycled
GO
-- Using currently connected connections:
SELECT distinct local_tcp_port
FROM sys.dm_exec_connections
WHERE local_tcp_port is not null
-- will also return records for DB Mirroring endpoints
GO
-- Using system registry (dynamic port):
DECLARE @portNo NVARCHAR(10)
EXEC xp_instance_regread
@rootkey = 'HKEY_LOCAL_MACHINE',
@key =
'Software\Microsoft\Microsoft SQL Server\MSSQLServer\SuperSocketNetLib\Tcp\IpAll',
@value_name = 'TcpDynamicPorts',
@value = @portNo OUTPUT
SELECT [PortNumber] = @portNo
GO
-- Using system registry (static port):
DECLARE @portNo NVARCHAR(10)
EXEC xp_instance_regread
@rootkey = 'HKEY_LOCAL_MACHINE',
@key =
'Software\Microsoft\Microsoft SQL Server\MSSQLServer\SuperSocketNetLib\Tcp\IpAll',
@value_name = 'TcpPort',
@value = @portNo OUTPUT
SELECT [PortNumber] = @portNo
GO
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment