Find TCP IP port in SQL Server 2012 by sys.dm_tcp_listener_states DMV
I have already explained different ways to find TCP port of SQL Server in one of my earlier article. We have already seen following different ways to find TCP port.
- XP_RegRead extended stored procedure
- From “SQL Server Configuration Manager”
- XP_ReadErrorLog extended stored procedure
These are the traditional ways which has been in use since long but SQL Server 2012 provides you one direct way by providing “sys.dm_tcp_listener_states” DMV.
Let us try executing simple SELECT statement on “sys.dm_tcp_listener_states” DMV.
SELECT listener_id, ip_address, is_ipv4, port, type_desc, state_desc, start_time FROM sys.dm_tcp_listener_states WITH (NOLOCK) GO
Here is the screen capture I have received from my laptop when I executed above SELECT statement.
Reference: Ritesh Shah
Note: Microsoft Books online is a default reference of all articles.