The sys.dm_tcp_listener_states DMV was introduced in version SQL Server 2012.
This is a great DMV because the SQL Configuration Manager does not have to opened to find these values. However some of the columns are not readable with out referencing the MSDN web site to find what the default values for the columns IP_ADDRESS and IS_IPV4:
sys.dm_tcp_listener_states (Transact-SQL)
Below is the sys.dm_tcp_listener_states DMV, which I altered to make the results more readable:
select listener_id, CASE ip_address when '::' THEN 'IPv6 Default All' when '0.0.0.0' THEN 'IPv4 Default All' ELSE ip_address END AS 'IP_ADDRESS', CASE is_ipv4 when 1 THEN 'IPv4' ELSE 'IPv6' END AS 'IP_TYPE', port, type_desc, state_desc, start_time from sys.dm_tcp_listener_states WITH (NOLOCK) OPTION (RECOMPILE);
Below the columns for ip_address and is_ipv4 have a more readable value:
If the SQL Configuration Manager is altered to exclude some IP addresses, then those updated values would be displayed in the IP_ADDRESS column:
Leave a Comment