Export (0) Print
Expand All
This topic has not yet been rated - Rate this topic

sys.dm_tcp_listener_states (Transact-SQL)

SQL Server 2012

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

Note 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

nvarchar48

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 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.

Community Additions

ADD
Show:
© 2014 Microsoft. All rights reserved.