sys.dm_broker_connections (Transact-SQL)
Collapse the table of content
Expand the table of content

sys.dm_broker_connections (Transact-SQL)

 

Updated: January 8, 2016

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

Returns a row for each Service Broker network connection. The following table provides more information:

Column nameData typeDescription
connection_iduniqueidentifierIdentifier of the connection. NULLABLE.
transport_stream_iduniqueidentifierIdentifier of the SQL Server Network Interface (SNI) connection used by this connection for TCP/IP communications. NULLABLE.
statesmallintCurrent state of the connection. NULLABLE. Possible values:

1 = NEW

2 = CONNECTING

3 = CONNECTED

4 = LOGGED_IN

5 = CLOSED
state_descnvarchar(60)Current state of the connection. NULLABLE. Possible values:

NEW

CONNECTING

CONNECTED

LOGGED_IN

CLOSED
connect_timedatetimeDate and time at which the connection was opened. NULLABLE.
login_timedatetimeDate and time at which login for the connection succeeded. NULLABLE.
authentication_methodnvarchar(128)Name of the Windows Authentication method, such as NTLM or KERBEROS. The value comes from Windows. NULLABLE.
principal_namenvarchar(128)Name of the login that was validated for connection permissions. For Windows Authentication, this value is the remote user name. For certificate authentication, this value is the certificate owner. NULLABLE.
remote_user_namenvarchar(128)Name of the peer user from the other database that is used by Windows Authentication. NULLABLE.
last_activity_timedatetimeDate and time at which the connection was last used to send or receive information. NULLABLE.
is_acceptbitIndicates whether the connection originated on the remote side. NULLABLE.

1 = The connection is a request accepted from the remote instance.

0 = The connection was started by the local instance.
login_statesmallintState of the login process for this connection. Possible values:

0 = INITIAL

1 = WAIT LOGIN NEGOTIATE

2 = ONE ISC

3 = ONE ASC

4 = TWO ISC

5 = TWO ASC

6 = WAIT ISC Confirm

7 = WAIT ASC Confirm

8 = WAIT REJECT

9 = WAIT PRE-MASTER SECRET

10 = WAIT VALIDATION

11 = WAIT ARBITRATION

12 = ONLINE

13 = ERROR
login_state_descnvarchar(60)Current state of login from the remote computer. Possible values:

Connection handshake is initializing.

Connection handshake is waiting for Login Negotiate message.

Connection handshake has initialized and sent security context for authentication.

Connection handshake has received and accepted security context for authentication.

Connection handshake has initialized and sent security context for authentication. There is an optional mechanism available for authenticating the peers.

Connection handshake has received and sent accepted security context for authentication. There is an optional mechanism available for authenticating the peers.

Connection handshake is waiting for Initialize Security Context Confirmation message.

Connection handshake is waiting for Accept Security Context Confirmation message.

Connection handshake is waiting for SSPI rejection message for failed authentication.

Connection handshake is waiting for Pre-Master Secret message.

Connection handshake is waiting for Validation message.

Connection handshake is waiting for Arbitration message.

Connection handshake is complete and is online (ready) for message exchange.

Connection is in error.
peer_certificate_idintThe local object ID of the certificate that is used by the remote instance for authentication. The owner of this certificate must have CONNECT permissions to the Service Broker endpoint. NULLABLE.
encryption_algorithmsmallintEncryption algorithm that is used for this connection. NULLABLE. Possible values:

 Value | Description | Corresponding DDL option

0 | none | Disabled

1 | SIGNING ONLY

2 | AES , RC4 | Required | Required algorithm RC4}

3 | AES |Required algorithm AES

 Note: The RC4 algorithm is only supported for backward compatibility. New material can only be encrypted using RC4 or RC4_128 when the database is in compatibility level 90 or 100. (Not recommended.) Use a newer algorithm such as one of the AES algorithms instead. In SQL Server 2012 and later versions, material encrypted using RC4 or RC4_128 can be decrypted in any compatibility level.
encryption_algorithm_descnvarchar(60)Textual representation of the encryption algorithm. NULLABLE. Possible Values:

 Description | Corresponding DDL option

NONE | Disabled

RC4 | {Required | Required Algorithm RC4}

AES | Required Algorithm AES

NONE, RC4 | {Supported | Supported Algorithm RC4}

NONE, AES | Supported Algorithm RC4

RC4, AES | Required Algorithm RC4 AES

AES, RC4 | Required Algorithm AES RC4

NONE, RC4, AES | Supported Algorithm RC4 AES

NONE, AES, RC4 | Supported Algorithm AES RC4
receives_postedsmallintNumber of asynchronous network receives that have not yet completed for this connection. NULLABLE.
is_receive_flow_controlledbitWhether network receives have been postponed due to flow control because the network is busy. NULLABLE.

1 = True
sends_postedsmallintThe number of asynchronous network sends that have not yet completed for this connection. NULLABLE.
is_send_flow_controlledbitWhether network sends have been postponed due to network flow control because the network is busy. NULLABLE.

1 = True
total_bytes_sentbigintTotal number of bytes that were sent by this connection. NULLABLE.
total_bytes_receivedbigintTotal number of bytes that were received by this connection. NULLABLE.
total_fragments_sentbigintTotal number of Service Broker message fragments that were sent by this connection. NULLABLE.
total_fragments_receivedbigintTotal number of Service Broker message fragments that were received by this connection. NULLABLE.
total_sendsbigintTotal number of network send requests that were issued by this connection. NULLABLE.
total_receivesbigintTotal number of network receive requests that were issued by this connection. NULLABLE.
peer_arbitration_iduniqueidentifierInternal identifier for the endpoint. NULLABLE.

Requires VIEW SERVER STATE permission on the server.

Joins for sys.dm_broker_connections

FromToRelationship
dm_broker_connections.connection_iddm_exec_connections.connection_idOne-to-one

Dynamic Management Views and Functions (Transact-SQL)
Service Broker Related Dynamic Management Views (Transact-SQL)

Community Additions

ADD
Show:
© 2016 Microsoft