· Self-Help Online Support will be available for a minimum of 12 months. Example: Microsoft online Knowledge Base articles, FAQs, troubleshooting tools, and other resources, that help customers resolve common issues.

· Updates to this software will stop and customers will no longer receive patches, including security updates.

What Are available Options :

· Upgrade to a supported version of SQL Server.

· Find out more about a Custom Support Agreement (CSA).

· Run SQL Server 2000 unsupported with access to Self-Help Online Support only (not recommended).

· Upgrade to SQL Server 2005 is not recommended as SQL Server 2005 is also transitioned from Mainstream Support to Extended Support.

Returns one row per authenticated session on SQL Server. sys.dm_exec_sessions is a server-scope view that shows information about all active user connections and internal tasks. This information includes client version, client program name, client login time, login user, current session setting, and more.

· Dormant – Session has been reset because of connection pooling and is now in prelogin state.

· Preconnect – Session is in the Resource Governor classifier.

2. All possible Transaction isolation level of the session, on basis on integer values :

· 0 = Unspecified

· 1 = ReadUncomitted

· 2 = ReadCommitted

· 3 = Repeatable

· 4 = Serializable

· 5 = Snapshot

3. Common Criteria Compliance Enabled : If this option in server configuration is enabled, logon statistics are displayed in the following columns. If this option is not enabled, these columns will return null values.

· last_successful_logon

· last_unsuccessful_logon

· unsuccessful_logons

4. Permissions : User required VIEW SERVER STATE permission on the server to see all executing sessions on the instance of SQL Server, otherwise, the user will see only the current session.

Returns information about the connections established to this instance of SQL Server and the details of each connection.

sys.dm_exec_connections is the most common DMV used to get connection details. We will get details info regarding every connection like protocol user, last read time, last write time, last executed SQL query, connection time etc.

Query 1 : Complete details of every SQL Connection

SELECT

C.SESSION_ID,

C.MOST_RECENT_SESSION_ID,

C.CONNECT_TIME,

C.LAST_READ,

C.LAST_WRITE,

C.NUM_READS,

C.NUM_WRITES,

C.NET_TRANSPORT,

C.ENCRYPT_OPTION,

C.AUTH_SCHEME,

C.PROTOCOL_TYPE,

C.PROTOCOL_VERSION,

C.NET_PACKET_SIZE,

C.ENDPOINT_ID,

C.CLIENT_NET_ADDRESS,

C.CLIENT_TCP_PORT,

C.LOCAL_NET_ADDRESS,

C.LOCAL_TCP_PORT,

C.NODE_AFFINITY,

C.CONNECTION_ID,

C.PARENT_CONNECTION_ID,

C.MOST_RECENT_SQL_HANDLE,

CASE WHEN ST.DBID = 32767 THEN ‘RESOURCEDB’ ELSE DB_NAME(ST.DBID) END AS DATABASE_NAME,

CASE WHEN ST.DBID IS NULL THEN NULL ELSE OBJECT_SCHEMA_NAME(ST.OBJECTID, ST.DBID) END AS OBJECT_SCHEMA_NAME,

CASE WHEN ST.DBID IS NULL THEN NULL ELSE OBJECT_NAME(ST.OBJECTID, ST.DBID) END AS OBJECT_NAME,

ST.TEXT AS QUERY_TEXT

FROM

SYS.DM_EXEC_CONNECTIONS C

CROSS APPLY SYS.DM_EXEC_SQL_TEXT(C.MOST_RECENT_SQL_HANDLE) ST

Query 2 : Sample Query Get a count of SQL connections by IP address

SELECT EC.CLIENT_NET_ADDRESS ,

ES.[PROGRAM_NAME] ,

ES.[HOST_NAME] ,

ES.LOGIN_NAME ,

COUNT(EC.SESSION_ID) AS [CONNECTION COUNT]

FROM SYS.DM_EXEC_SESSIONS AS ES

INNER JOIN SYS.DM_EXEC_CONNECTIONS AS EC

ON ES.SESSION_ID = EC.SESSION_ID

GROUP BY EC.CLIENT_NET_ADDRESS ,

ES.[PROGRAM_NAME] ,

ES.[HOST_NAME] ,

ES.LOGIN_NAME

ORDER BY EC.CLIENT_NET_ADDRESS ,

ES.[PROGRAM_NAME] ;

Permissions : User required VIEW SERVER STATE permission on the server, to use this DMV.