Edit

sys.dm_tcp_listener_states (Transact-SQL)

  • Article

Applies to: SQL Server

Returns a row containing dynamic-state information for each TCP listener.

Note

The availability group listener could listen to the same port as the listener of the instance of SQL Server. In this case, the listeners are listed separately, the same as for a Service Broker listener.

Column name Data type Description
listener_id int Listener's internal ID. Is not nullable.

Primary key.
ip_address nvarchar(48) The listener IP address that is online and currently being listening to. Either IPv4 and IPv6 is allowed. If a listener possesses both types of addresses, they are listed separately. An IPv4 wildcard, is displayed as "0.0.0.0". An IPv6 wildcard, is displayed as "::".

Is not nullable.
is_ipv4 bit Type of IP address

1 = IPv4

0 = IPv6
port int The port number on which the listener is listening. Is not nullable.
type tinyint Listener type, one of:

0 = Transact-SQL

1 = Service Broker

2 = Database mirroring

Is not nullable.
type_desc nvarchar(20) Description of the type, one of:

TSQL

SERVICE_BROKER

DATABASE_MIRRORING

Is not nullable.
state tinyint State of the availability group listener, one of:

1 = Online. The listener is listening and processing requests.

2 = Pending restart. the listener is offline, pending a restart.

If the availability group listener is listening to the same port as the server instance, these two listeners always have the same state.

Is not nullable.

Note: The values in this column come from the TSD_listener object. The column does not support an offline state because when the TDS_listener is offline, it cannot be queried for state.
state_desc nvarchar(16) Description of state, one of:

ONLINE

PENDING_RESTART

Is not nullable.
start_time datetime Timestamp indicating when the listener was started. Is not nullable.

Permissions

Requires VIEW SERVER STATE permission on the server.

Permissions for SQL Server 2022 and later

Requires VIEW SERVER SECURITY STATE permission on the server.

See also

Querying the SQL Server System Catalog FAQ
Always On Availability Groups Catalog Views (Transact-SQL)
Always On Availability Groups Dynamic Management Views and Functions (Transact-SQL)