sys.dm_tcp_listener_states (Transact-SQL)

 

Updated: June 10, 2016

THIS TOPIC APPLIES TO: yesSQL Server (starting with 2012)noAzure SQL DatabasenoAzure SQL Data Warehouse noParallel Data Warehouse

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

System_CAPS_ICON_note.jpg 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 nameData typeDescription
listener_idintListener’s internal ID. Is not nullable.

Primary key.
ip_addressnvarchar48The 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_ipv4bitType of IP address

1 = IPv4

0 = IPv6
portintThe port number on which the listener is listening. Is not nullable.
typetinyintListener type, one of:

0 = Transact-SQL

1 = Service Broker

2 = Database mirroring

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

TSQL

SERVICE_BROKER

DATABASE_MIRRORING

Is not nullable.
statetinyintState 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_descnvarchar(16)Description of state, one of:

ONLINE

PENDING_RESTART

Is not nullable.
start_timedatetimeTimestamp indicating when the listener was started. Is not nullable.

Permissions

Requires VIEW SERVER STATE permission on the server.

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)

Community Additions

ADD
Show: