What Is Consuming My Log Space

October 2, 2015

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 besides all the active transactions.

Such as:

Recovery Model

If replication/Log Shipping/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.text ,
  [statement] =
  COALESCE(NULLIF(SUBSTRING(t.text,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.text)
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 the DBCC OPENTRAN command for a single database. But I wanted to find it for all databases that have open transactions 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.

6 replies on “What Is Consuming My Log Space”

The first query gives me:

Msg 102, Level 15, State 1, Line 8
Incorrect syntax near ‘.1’.

What am I doing wrong?

I fixed the query as mentioned by Balaji. It was a HTML formatting issue.

the first query is not working

I fixed the query as mentioned by Balaji. It was a HTML formatting issue.

Thanks for the queries. 1st query is getting the errors as there is t.1 refers to t.text . So I have modified the query as below..
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.text ,
[statement] = COALESCE(NULLIF(SUBSTRING(t.text,
r.statement_start_offset / 2,
CASE WHEN r.statement_end_offset 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;

Thank you so much. Fixed the query.