How to get the IP address, computer name and other information of the front-end connection in SQL Server

Source: Internet
Author: User
Tags session id

In some requirements, it may be necessary to know some system information about the front-end programs connected to SQL Server, such as the computer name of the front-end connection, the IP address, when to start requesting the connection, and when to end the connection.

If you are not familiar with SQL Server's system functions or views, this functionality may seem more complex, but in fact, the dynamic management view of SQL Server has given us this information, let's take a look at two dynamic management views.

1, sys.dm_exec_sessions
This view provides some information about all the clients that connect to SQL Server, following the columns returned by sys.dm_exec_sessions:

Column Name Data Type Description
session_id smallint Identifies the session associated with each active primary connection.
Login_time Datetime The time the session was established.
Host_name nvarchar (128) The host associated with the session.
Program_name nvarchar (128) The program associated with the session.
host_process_id Int The process ID associated with the session.
Client_version Int The version of the interface used by the client to connect to the server.
Client_interface_name nvarchar (32) The name of the interface used by the client to connect to the server.
security_id varbinary (85) The Microsoft Windows security ID that is associated with the login name.
Login_name nvarchar (128) The SQL logon name associated with the session.
Nt_domain nvarchar (128) The domain from which the session connection was established.
Nt_user_name nvarchar (128) The user name associated with the session.
Status nvarchar (30) The state of the session. Possible values: 1, run-one or more requests are currently running
2, sleep-no requests are currently running
3, Hibernate-session is in pre-logon state
Context_info varbinary (128) The context_info value of the session.
Cpu_time Int The CPU time (in milliseconds) that the session occupies.
Memory_usage Int The number of 8 KB memory pages that the session occupies.
Total_scheduled_time Int The total amount of time (in milliseconds) that is spent on the execution of the scheduled session that contains the request.
Total_elapsed_time Int Elapsed time (in milliseconds) since the session was established.
endpoint_id Int ID of the endpoint associated with the session.
Last_request_start_time Datetime The start time of the most recent session request. This includes the request that is currently executing.
Last_request_end_time Datetime The time when the last session request was completed.
Reads bigint The number of reads performed by the request in the session during the session.
Writes bigint The number of writes performed by the request in the session during the session.
Logical_reads bigint The number of logical reads that have been performed on the session.
Is_user_process Bit 0 if the session is a system session. Otherwise, it is 1.
Text_size Int The TEXTSIZE setting for the session.
Language nvarchar (128) The LANGUAGE setting for the session.
Date_format nvarchar (3) The DateFormat setting for the session.
Date_first smallint The Datefirst setting for the session.
Quoted_identifier Bit The QUOTED_IDENTIFIER setting for the session.
Arithabort Bit The ARITHABORT setting for the session.
ansi_null_dflt_on Bit The ANSI_NULL_DFLT_ON setting for the session.
Ansi_defaults Bit The ANSI_DEFAULTS setting for the session.
Ansi_warnings Bit The ANSI_WARNINGS setting for the session.
Ansi_padding Bit The ANSI_PADDING setting for the session.
Ansi_nulls Bit The ANSI_NULLS setting for the session.
Concat_null_yields_null Bit The CONCAT_NULL_YIELDS_NULL setting for the session.
Transaction_isolation_level smallint The transaction isolation level of the session. 0 = unspecified 1 = uncommitted Read 2 = Read Committed 3 = repeatable 4 = Serializable 5 = Snapshot
Lock_timeout Int The LOCK_TIMEOUT setting for the session. The value is measured in milliseconds.
Deadlock_priority Int The Deadlock_priority setting for the session.
Row_count bigint The number of rows returned by the session so far.
Prev_error Int The ID of the most recent error returned by the session.

For example, we want to see those hosts connected to SQL Server servers, you can use the following SQL statement:

Select distinct host_name from sys.dm_exec_sessions

To see those users connected to SQL Server servers:

Select distinct login_name from sys.dm_exec_sessions

Of course, using the columns above, we can get more client information we want.

2, Sys.dm_exec_connections
This view returns the details of each connection connected to the SQL Server servers, and the following is the column returned by Sys.dm_exec_connections:

Column Name Data Type Description
session_id Int Identifies the session associated with this connection.
most_recent_session_id Int Displays the session ID of the most recent request associated with this connection.
Connect_time Datetime Timestamp when the connection was established.
Net_transport nvarchar (40) Describes the physical transport protocol used by this connection.
Protocol_type nvarchar (40) Specifies the protocol type of the payload. This parameter is currently distinguishable from TDS (TSQL) and SOAP.
Protocol_version Int The version of the data access protocol associated with this connection.
endpoint_id Int Unique identifier of the endpoint associated with this connection. This endpoint_id can be used to query the sys.endpoints view.
Encrypt_option nvarchar (40) A Boolean value that indicates whether encryption is enabled for this connection.
Auth_scheme nvarchar (40) Specifies the SQL server/nt authentication to use with this connection.
Node_affinity smallint Displays the SOS node associated with this connection.
Num_reads Int The number of read packets that have occurred in this connection.
Num_writes Int The number of write packets that have occurred in this connection.
Last_read Datetime Timestamp of the last read operation occurred in this connection.
Last_write Datetime Timestamp of the last write operation in this connection.
Net_packet_size Int The size of the network packet used for information and data.
Client_net_address varchar (40) The host address of the client to which this server is connected.
Client_tcp_port Int The port number on the client that is associated with the connection.
Local_net_address varchar (40) Displays the IP address of the destination server for this connection. Available only for connections that use the TCP transport provider.
Local_tcp_port Int If this connection uses a TCP transport, the TCP port of the destination server for that connection is displayed.
connection_id uniqueidentifier Each connection is uniquely identified.
parent_connection_id uniqueidentifier Identifies the primary connection that the MARS session is using.
Most_recent_sql_handle varbinary (64) The SQL handle of the last request executed on this connection. The Most_recent_sql_handle column is always synchronized with the most_recent_session_id column.

For example, I want to see the IP of the client IP that is currently connected and the server on which SQL Servers are located, using the following SQL query:

Select client_net_address ' Client IP ', local_net_address ' server IP ' from sys.dm_exec_connections where [email protected] @spid

The function of the @ @spid is to return the session ID of the current process.

How to get the IP address, computer name and other information of the front-end connection in SQL Server

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.