Active Processes

ActiveProcesses.zip

This time I'll show you a script developed to cature running processes details.

There are a number of similar scripts out of there, but I've tried to use a single query based on DMVs only (no more sys.sysprocesses).

Exluding the deprecated sys.sysprocesses was the only tricky task, because there is no straight way to obtain the database_id unless we are using Sql Server 2012 which added this column to sys.dm_exec_sessions.

Fortunately sys.dm_tran_locks contains the information we need.

SET TRANSACTION isolation level READ uncommitted

SELECT CASE
         WHEN ER.total_elapsed_time > 0 THEN
         CONVERT(VARCHAR, (ER.total_elapsed_time - ER.total_elapsed_time %
         3600000) /
         3600000)
         + ':'
         + RIGHT('00' + CONVERT(VARCHAR, (ER.total_elapsed_time -
         ER.total_elapsed_time
         % 60000) % 3600000 /
         60000), 2)
         + ':'
         + RIGHT('00' + CONVERT(VARCHAR, (ER.total_elapsed_time -
         ER.total_elapsed_time
         % 1000) % 60000 / 1000), 2)
         + '.'
         + RIGHT('000' + CONVERT(VARCHAR, ER.total_elapsed_time % 1000), 3)
       END
       elapsed_time,
       ES.session_id,
       NULLIF(ER.blocking_session_id, 0)
       blocking_session_id,
       ES.[status],
       Db_name(COALESCE(ER.database_id, TL.resource_database_id, 1))
       database_name,
       ER.cpu_time,
       ER.reads,
       ER.writes,
       ER.logical_reads,
       ER.wait_time,
       ER.wait_type,
       ER.last_wait_type,
       ES.total_elapsed_time,
       ES.cpu_time
       total_cpu_time,
       ES.reads                                                      total_reads
       ,
       ES.writes
       total_writes,
       ES.logical_reads
       total_logical_reads,
       CASE
         WHEN Isnull(ER.open_transaction_count, 0) > 0 THEN
         ER.open_transaction_count
         WHEN ST.open_tran > 0 THEN ST.open_tran
       END
       open_transaction_count,
       CASE Isnull(ER.transaction_isolation_level,
            ES.transaction_isolation_level)
         WHEN 0 THEN 'Unspecified'
         WHEN 1 THEN 'ReadUncomitted'
         WHEN 2 THEN 'ReadCommitted'
         WHEN 3 THEN 'Repeatable'
         WHEN 4 THEN 'Serializable'
         WHEN 5 THEN 'Snapshot'
       END
       transaction_isolation,
       AT.name
       transaction_name,
       CASE AT.transaction_type
         WHEN 1 THEN 'read/write'
         WHEN 2 THEN 'read-only'
         WHEN 3 THEN 'system'
         WHEN 4 THEN 'distributed'
       END
       transaction_type,
       CASE AT.transaction_state
         WHEN 0 THEN 'not completely initialized'
         WHEN 1 THEN 'initialized but not started'
         WHEN 2 THEN 'active'
         WHEN 3 THEN 'read-only transaction has ended'
         WHEN 4 THEN 'commit initiated'
         WHEN 5 THEN 'prepared and waiting resolution'
         WHEN 6 THEN 'committed'
         WHEN 7 THEN 'being rolled back'
         WHEN 8 THEN 'been rolled back'
       END
       transaction_state,
       ES.last_request_start_time,
       ES.last_request_end_time,
       ES.[host_name],
       ES.login_time,
       ES.login_name,
       ES.[language],
       ES.date_format,
       ES.date_first,
       ES.[program_name],
       ER.command,
       ET.[text]
       sql_statement
FROM   sys.dm_exec_connections EC
       INNER JOIN sys.dm_exec_sessions ES
               ON ES.session_id = EC.session_id
       LEFT JOIN sys.dm_exec_requests ER
              ON ER.session_id = EC.session_id
       LEFT JOIN (SELECT Count(*) open_tran,
                         XT.session_id
                  FROM   sys.dm_tran_session_transactions XT
                  GROUP  BY XT.session_id) ST
              ON ST.session_id = ES.session_id
       LEFT JOIN sys.dm_tran_active_transactions AT
              ON AT.transaction_id = ER.transaction_id
       OUTER apply sys.Dm_exec_sql_text(Isnull(ER.sql_handle,
                                        EC.most_recent_sql_handle)) ET
       OUTER apply (SELECT TOP 1 XL.resource_database_id
                    FROM   sys.dm_tran_locks XL
                    WHERE  XL.resource_type = 'DATABASE'
                           AND XL.request_session_id = EC.session_id) TL
WHERE  EC.most_recent_sql_handle > 0x00
ORDER  BY ER.total_elapsed_time DESC,
          EC.session_id

This script will be included in the next version of SqlDbAid.