T-SQL: Identify queries that consume a large amount of log space

Well, SQL statements can use a log of log space, and you most probably want to find out which SQL statement is using all of your log space. The T-SQL code below will show you the SQL statements using the log space, including the size that it’s using.

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;