One of regular issues DBA’s get are about the T-log growth. Situations, wherein one “bad” or “poorly-designed” query can eat up entire T-log space, bring the free space to zero and then bring your application down. The cause and remedy of most these issue is discussed in this KB # 317375(I’m big fan on Microsoft KB’s).
While the KB discussed about the causes and approaches to deal with high T-log growth situations, it also hints about how we can ‘proactively’ find the queries that are consuming your T-log space at any given moment using DMV’s. Taking cue from this, I have written a below T-SQL Code:
Identify queries consuming large T-log space:
USE <your_database_name>
go
-- Description: get queries consuming maximum T-log space
-- Source: based on KB317375
-- Author: varun.dhawan@gmail.com
SELECT dtst.session_id AS 'spid' ,
Cast(Db_name(dtdt.database_id) AS VARCHAR(20)) AS 'database' ,
der.command ,
Substring(st.text, ( der.statement_start_offset / 2 ) + 1 , ((
CASE der.statement_end_offset
WHEN -1 THEN Datalength(st.text)
ELSE der.statement_end_offset
END - der.statement_start_offset)/2)+1) AS 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)), '') AS command_text ,
der.wait_type ,
der.wait_time ,
dtdt.database_transaction_log_bytes_used / 1024.0 / 1024.0 AS 'mb used' ,
dtdt.database_transaction_log_bytes_used_system / 1024.0 / 1024.0 AS 'mb used system' ,
dtdt.database_transaction_log_bytes_reserved / 1024.0 / 1024.0 AS 'mb reserved' ,
dtdt.database_transaction_log_bytes_reserved_system / 1024.0 / 1024.0 AS 'mb reserved system' ,
dtdt.database_transaction_log_record_count AS 'record count'
FROM sys.dm_tran_database_transactions dtdt
JOIN sys.dm_tran_session_transactions dtst
ON dtdt.transaction_id = dtst.transaction_id
JOIN sys.dm_exec_requests der
CROSS apply sys.Dm_exec_sql_text(der.sql_handle) AS st
ON dtst.session_id = der.session_id
ORDER BY 8 DESC;
Disclaimer: Everything here, is my personal opinion and is not read or approved by my employer before it is posted. No warranties or other guarantees will be offered as to the quality of the opinions or anything else offered here.
data-nerd.blog 
Good one varun
Thanks Sufian
the SSMS complain about this :
“Msg 102, Level 15, State 1, Line 22
Incorrect syntax near ‘–’.
”
which is
END –
and SSMS also compliain about:
“Msg 102, Level 15, State 1, Line 23
Incorrect syntax near ‘der’.
”
any word missing in between :
” ELSE der.statement_end_offset
END –
der.statement_start_offset ) / 2 ) +”
Good One Varun. I used it successfully today. Thanks .
Thanks @Sarma!
Varun..But it is giving data only for actively running queries..I want all queries which have consumed my log space in past also..Because , the problme I am facing is that I am hosting my client’s data on my Cloud server and also ensuring their backups thround transaction log shipping…But their heavy logs are being backed up every 15 minutes…I want to avoid this..