Skip to main content
Our Tech Ideas

Finding Active Sessions in SQL Server

Finding Active Sessions in SQL Server


In this blog post, we will dive into the world of active sessions and requests in SQL Server. We will learn how to extract valuable information about these sessions using a T-SQL query. Don’t worry if you’re new to SQL Server.

Understanding Active Sessions

When users interact with a SQL Server database, they establish sessions. These sessions represent the connection between the user and the database server. Within each session, users can send requests to the server, such as executing queries or performing updates.

The SQL Query to find Active Sessions

Let’s take a look at the T-SQL query we will be discussing:

    ss.session_id AS SPID, -- Session ID
    original_login_name, -- Original login name
    sp.status, -- Status
    blocking_session_id AS [Blk by], -- Blocking session ID
    command, -- Command
    db_name(sp.database_id) AS [Database], -- Database name
    [objectid], -- Object ID
    percent_complete AS [% Done], -- Percentage of completion
        WHEN DATEDIFF(mi, start_time, GETDATE()) > 60 THEN CONVERT(varchar(4), (DATEDIFF(mi, start_time, GETDATE()) / 60)) + ' hr ' -- Duration: hours
        ELSE ''
    END +
        WHEN DATEDIFF(ss, start_time, GETDATE()) > 60 THEN CONVERT(varchar(4), (DATEDIFF(mi, start_time, GETDATE()) % 60)) + ' min ' -- Duration: minutes
        ELSE ''
    END +
    CONVERT(varchar(4), (DATEDIFF(ss, start_time, GETDATE()) % 60)) + ' sec' AS [Duration], -- Duration: seconds
    estimated_completion_time / 60000 AS [ETA (Min)], -- Estimated time of completion in minutes
    [text] AS [input stream/text], -- Input stream or text
    SUBSTRING([text], statement_start_offset / 2 + 1,
                      WHEN statement_end_offset < 0 THEN (LEN(CONVERT(nvarchar(max), [text])) * 2) -- Executing SQL statement
                      ELSE statement_end_offset
                  END) - statement_start_offset) / 2 + 1) AS [Executing_sql_statement],
    wait_resource, -- Wait resource
    wait_time / 1000 AS [wait_time (sec)], -- Wait time in seconds
    last_wait_type, -- Last wait type
    login_time, -- Login time
    last_request_start_time, -- Last request start time
    last_request_end_time, -- Last request end time
    host_name, -- Host name
        WHEN program_name LIKE 'SQLAgent%Job%' THEN (
            SELECT TOP 1 '(SQLAgent Job - ' + name + ' - ' + RIGHT(program_name, LEN(program_name) - CHARINDEX(':', program_name)) -- Program name: SQL Agent job
            FROM msdb.dbo.sysjobs SJ
            WHERE UPPER(master.dbo.fn_varbintohexstr(SJ.job_id)) = UPPER(SUBSTRING([program_name], 30, 34))
        ELSE program_name
    END AS [program_name], -- Program name
    sp.open_transaction_count, -- Open transaction count
    CASE sp.transaction_isolation_level
        WHEN 0 THEN 'Unspecified' -- Transaction isolation level: Unspecified
        WHEN 1 THEN 'ReadUncomitted' -- Transaction isolation level: Read Uncommitted
        WHEN 2 THEN 'ReadCommitted' -- Transaction isolation level: Read Committed
        WHEN 3 THEN 'Repeatable' -- Transaction isolation level: Repeatable
        WHEN 4 THEN 'Serializable' -- Transaction isolation level: Serializable
        WHEN 5 THEN 'Snapshot' -- Transaction isolation level: Snapshot
    END AS [transaction_isolation_level],
    sp.cpu_time, -- CPU time
    sp.reads, -- Reads
    sp.writes, -- Writes
    sp.logical_reads, -- Logical reads
    sp.lock_timeout, -- Lock timeout
    sp.row_count -- Row count
FROM sys.dm_exec_requests AS sp
OUTER APPLY sys.dm_exec_sql_text(sp.sql_handle) AS esql
RIGHT OUTER JOIN sys.dm_exec_sessions ss ON ss.session_id = sp.session_id
WHERE ss.status <> 'sleeping';


Understanding and monitoring active sessions and requests in SQL Server is crucial for database administrators and developers. By using the provided T-SQL query, we can gather valuable insights into ongoing activities, performance metrics, and potential issues within the server.

More post on Active Sessions in SQL Server