SQL Server: find long-running transactions and requests
DMV queries to find the transactions that hold the log and the longest-running requests.
Queries to find the open transactions that hold the transaction log, and the requests that consume the most elapsed time. Reference articles on long-running query alerts are linked at the end.
Long transactions and log usage
SELECT b.session_id 'Session ID'
, CAST(Db_name(a.database_id) AS VARCHAR(20)) 'Database Name'
, c.command
, Substring(st.TEXT, ( c.statement_start_offset / 2 ) + 1,
((
CASE c.statement_end_offset
wHEN -1 THEN Datalength(st.TEXT)
ELSE c.statement_end_offset
END
-
c.statement_start_offset ) / 2 ) + 1) statement_text
, Coalesce(Quotename(Db_name(st.dbid)) + N'.' + Quotename(
Object_schema_name(st.objectid,
st.dbid)) +
N'.' + Quotename(Object_name(st.objectid, st.dbid)), '') command_text
, c.wait_type
, c.wait_time
, a.database_transaction_log_bytes_used / 1024.0 / 1024.0 'MB used'
, a.database_transaction_log_bytes_used_system / 1024.0 / 1024.0 'MB used system'
, a.database_transaction_log_bytes_reserved / 1024.0 / 1024.0 'MB reserved'
, a.database_transaction_log_bytes_reserved_system / 1024.0 / 1024.0 'MB reserved system'
, a.database_transaction_log_record_count 'Record count'
FROM
sys.dm_tran_database_transactions a
JOIN sys.dm_tran_session_transactions b ON a.transaction_id = b.transaction_id
JOIN sys.dm_exec_requests c CROSS APPLY sys.Dm_exec_sql_text(c.sql_handle) AS st ON b.session_id = c.session_id
ORDER BY 'MB used' DESC
Execution plan and statistics of a query from its handle
Replace 0x<SQL_HANDLE> with a sql_handle taken from sys.dm_exec_requests or sys.dm_exec_query_stats.
SELECT qp.query_plan, sql_handle, plan_handle, creation_time, last_execution_time, plan_generation_num, execution_count
FROM sys.dm_exec_query_stats eqs
CROSS APPLY sys.dm_exec_sql_text(eqs.sql_handle) AS st
CROSS APPLY sys.dm_exec_query_plan(eqs.plan_handle) AS qp
WHERE eqs.sql_handle=0x<SQL_HANDLE>
ORDER BY eqs.last_execution_time DESC
Running requests with their plan
SELECT
r.session_id
, r.start_time
, TotalElapsedTime_ms = r.total_elapsed_time
, r.[status]
, r.command
, DatabaseName = DB_Name(r.database_id)
, r.wait_type
, r.last_wait_type
, r.wait_resource
, r.cpu_time
, r.reads
, r.writes
, r.logical_reads
, t.[text] AS [executing batch]
, SUBSTRING(
t.[text], r.statement_start_offset / 2,
( CASE WHEN r.statement_end_offset = -1 THEN DATALENGTH (t.[text])
ELSE r.statement_end_offset
END - r.statement_start_offset ) / 2
) AS [executing statement]
, p.query_plan
FROM
sys.dm_exec_requests r
CROSS APPLY
sys.dm_exec_sql_text(r.sql_handle) AS t
CROSS APPLY
sys.dm_exec_query_plan(r.plan_handle) AS p
ORDER BY
r.total_elapsed_time DESC;
Other tools
- Activity Monitor (SSMS)
- Server standard reports
- Database standard reports
Notes
ORDER BY 'MB used'sorts on a string literal, not on the column. To rank by log volume, useORDER BY a.database_transaction_log_bytes_used DESC.- A
sql_handleis specific to an instance and to a plan in the cache: it cannot be reused from one server to another. - Reference articles: SQL Server long running queries email alert and Set long running transaction alert.