Here are few important TSQL queries to get the long running transactions and queries in TempDB for monitoring purposes.
- Long-running transaction:
Sometimes transactions may run for long time preventing the shrinking of version store data.
The below queries will list the transactions that are running for long time.
SELECT top 10 transaction_id as [Transacton ID], transaction_sequence_num as [Transation Sequence Number], elapsed_time_seconds as [Elapsed Time (in sec)] FROM sys.dm_tran_active_snapshot_database_transactions ORDER BY elapsed_time_seconds DESC
A long running transaction may prevent cleanup of transaction log thus eating up all log space available resulting space crisis for all other applications.
select transaction_id as [Transacton ID], [name] as [Transaction Name], transaction_begin_time as [Transaction Begin Time], datediff(mi, transaction_begin_time, getdate()) as [Elapsed Time (in Min)], case transaction_type when 1 then 'Read/write' when 2 then 'Read-only' when 3 then 'System' when 4 then 'Distributed' end as [Transaction Type], case transaction_state when 0 then 'The transaction has not been completely initialized yet.' when 1 then 'The transaction has been initialized but has not started.' when 2 then 'The transaction is active.' when 3 then 'The transaction has ended. This is used for read-only transactions.' when 4 then 'The commit process has been initiated on the distributed transaction. This is for distributed transactions only. The distributed transaction is still active but further processing cannot take place.' when 5 then 'The transaction is in a prepared state and waiting resolution.' when 6 then 'The transaction has been committed.' when 7 then 'The transaction is being rolled back.' when 8 then 'The transaction has been rolled back.' end as [Transaction Description] from sys.dm_tran_active_transactions
- Long running Queries:
sys.dm_exec_requests : Returns information regarding the requests made to the database server.
select host_name as [System Name], program_name as [Application Name], DB_NAME(database_id) as [Database Name], User_Name(user_id) as [User Name], connection_id as [Connection ID], sys.dm_exec_requests.session_id as [Current Session ID], blocking_session_id as [Blocking Session ID], start_time as [Request Start Time], sys.dm_exec_requests.status as [Status], command as [Command Type], (select text from sys.dm_exec_sql_text(sql_handle)) AS [Query Text], wait_type as [Waiting Type], wait_time as [Waiting Duration], wait_resource as [Waiting for Resource], sys.dm_exec_requests.transaction_id as [Transaction ID], percent_complete as [Percent Completed], estimated_completion_time as [Estimated Completion Time (in mili sec)], sys.dm_exec_requests.cpu_time as [CPU time used (in mili sec)], (memory_usage * 8 ) as [Memory Usage (in KB)], sys.dm_exec_requests.total_elapsed_time as [Elapsed Time (in mili sec)] from sys.dm_exec_requests inner join sys.dm_exec_sessions on sys.dm_exec_requests.session_id = sys.dm_exec_sessions.session_id where DB_NAME(database_id) = 'tempdb'
Status: Status of the request.
This can be from one of the following options:
Command: Identifies the current type of command that is being processed.
Common command types include the following: