Identify queries that consume a large amount of log space in SQL Server

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:

——————————————————————————————-
– Description: T-SQL to find queries that consume a large amount of log space in SQL Server
– Source: KB # 317375
– Author: varun.dhawan
——————————————————————————————-
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
       ‘Rec 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 

Hope that this will help you too!

 

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.