What Is Consuming My Log Space

Often times DBA’s (including myself at the beginning of my DBA career) struggle to find out what is causing the log files to grow. Or what is consuming all the space in my log files. I understand there are many factors that can impact the log file size beside all the active transactions.

Such as:

  • Recovery Model
  • If replication/logshipping/mirroring is set up
  • Frequency of transaction log back up
  • Longest running transaction
  • and many more

I wanted to see a list of all the sessions that are consuming more than 1kb off log space for all the databases in the server.

SELECT  DB_NAME(tdt.database_id) AS [dbName] ,
        ( tdt.database_transaction_log_bytes_reserved
          + tdt.database_transaction_log_bytes_used
          + tdt.database_transaction_log_bytes_used_system
          + tdt.database_transaction_log_bytes_reserved_system ) / 1024 AS [tlogUsedKB] ,
        tst.session_id ,
        CONVERT(TIME(2), ( GETDATE() - tdt.database_transaction_begin_time )) AS [durInLog hh:mm:ss] ,
        t.1 ,
        [statement] = COALESCE(NULLIF(SUBSTRING(t.1,
                                                r.statement_start_offset / 2,
                                                CASE WHEN r.statement_end_offset < r.statement_start_offset
                                                     THEN 0
                                                     ELSE ( r.statement_end_offset
                                                            - r.statement_start_offset )
                                                          / 2
                                                END), ''), t.1)
FROM    sys.dm_tran_database_transactions AS tdt
        INNER JOIN sys.dm_tran_session_transactions AS tst ON tdt.transaction_id = tst.transaction_id
		        LEFT OUTER JOIN sys.dm_exec_requests AS r ON tst.session_id = r.session_id
        OUTER APPLY sys.dm_exec_sql_text(r.plan_handle) AS t
WHERE   tdt.database_transaction_log_bytes_used > 0
        AND ( tdt.database_transaction_log_bytes_reserved
              + tdt.database_transaction_log_bytes_used
              + tdt.database_transaction_log_bytes_used_system
              + tdt.database_transaction_log_bytes_reserved_system ) / 1024 > 0
        AND tdt.database_transaction_log_bytes_used > 0
ORDER BY tlogUsedKB DESC;

Next logical step will be to see the oldest active transaction. It is easy and simple to find by running DBCC OPENTRAN command for a single database. But I wanted to find it for all databases that has open transaction at one go. I want to acknowledge my colleague Suneel Mundlapudi helped with writing the query.

SET NOCOUNT ON;

IF OBJECT_ID(N'tempdb..#OpenTranStatus', N'U') IS NOT NULL
    DROP TABLE #OpenTranStatus;
GO
IF OBJECT_ID(N'tempdb..#OpenTranStatusStg', N'U') IS NOT NULL
    DROP TABLE #OpenTranStatusStg;
GO

-- Create the temporary table to accept the results.
CREATE TABLE #OpenTranStatus
    (
      -- dbname NVARCHAR(40),
      dbname VARCHAR(100) ,
      ActiveTransaction VARCHAR(25) ,
      Details SQL_VARIANT
    );

CREATE TABLE #OpenTranStatusStg
    (
      -- dbname NVARCHAR(40),
      ActiveTransaction VARCHAR(25) ,
      Details SQL_VARIANT
    );

DECLARE @dbid INT ,
    @dbname NVARCHAR(50) ,
    @SQLStmt NVARCHAR(1000);

DECLARE db_cursor CURSOR
FOR
    SELECT  database_id dbid ,
            name dbname
    FROM    sys.databases
    WHERE   state_desc = 'ONLINE'
    ORDER BY name;

OPEN db_cursor;

FETCH NEXT FROM db_cursor 
INTO @dbid, @dbname;

WHILE @@FETCH_STATUS = 0
    BEGIN
        TRUNCATE TABLE #OpenTranStatusStg;  
        SET @SQLStmt = 'DBCC OPENTRAN (' + CAST(@dbid AS NVARCHAR(100))
            + ') WITH TABLERESULTS, NO_INFOMSGS';
        INSERT  INTO #OpenTranStatusStg
                EXEC ( @SQLStmt
                    );
        INSERT  INTO #OpenTranStatus
                SELECT  @dbname ,
                        *
                FROM    #OpenTranStatusStg;

        -- Get the next vendor.
        FETCH NEXT FROM db_cursor 
    INTO @dbid, @dbname;
    END; 
CLOSE db_cursor;
DEALLOCATE db_cursor;

SELECT  *
FROM    #OpenTranStatus;

I hope these 2 queries will help you pin down what is causing your transaction log file to grow or find out what is consuming all the space in your transaction log file.
 

FacebookTwitterGoogle+Share