DMV to identify active connections to a database

In SQL Server there are always DMV’s and stored proc’s available to identify active connections to a database. One such inbuilt stored procedure that result sets the connections of a database is sp_who2. Also one of the most commonly used DMV that provides us with who is actively connected to a database at any given point of time is sys.dm_exec_connections dynamic management view. Even though this DMV provides us with information like most recent sql handle it does not provide the actual sql,the database  name that the session is connected to or the object name. But this can be achieved by cross applying the result set of the latter DMV with the table valued function SYS.DM_EXEC_SQL_TEXT passing the most recent sql handle as an argument. The query is posted below:


SELECT
DMEC.SESSION_ID,
DMEC.MOST_RECENT_SESSION_ID,
DMEC.CONNECT_TIME,
DMEC.LAST_READ,
DMEC.LAST_WRITE,
DMEC.NUM_READS,
DMEC.NUM_WRITES,
DMEC.CONNECTION_ID,
DMEC.PARENT_CONNECTION_ID,
DMEC.MOST_RECENT_SQL_HANDLE,
CASE WHEN DMEST.DBID = 32767 THEN 'RESOURCEDB' ELSE DB_NAME(DMEST.DBID) END AS DATABASE_NAME,
CASE WHEN DMEST.DBID IS NULL THEN NULL ELSE OBJECT_SCHEMA_NAME(DMEST.OBJECTID, DMEST.DBID) END AS OBJECT_SCHEMA_NAME,
CASE WHEN DMEST.DBID IS NULL THEN NULL ELSE OBJECT_NAME(DMEST.OBJECTID, DMEST.DBID) END AS OBJECT_NAME,
DMEST.TEXT AS 'SQLText'
FROM
SYS.DM_EXEC_CONNECTIONS DMEC
CROSS APPLY SYS.DM_EXEC_SQL_TEXT(DMEC.MOST_RECENT_SQL_HANDLE) DMEST

Hope this helps.

Advertisements
This entry was posted in SQL Server DBA Stuff and tagged , , , , , , , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s