What SQL Statements Are Currently Using The Transaction Logs?

  • ianstirk

    Ten Centuries

    Points: 1310

    Comments posted to this topic are about the item What SQL Statements Are Currently Using The Transaction Logs?

  • miamikk204

    SSC Enthusiast

    Points: 171

    Is the "SpaceUsed" value is in MB (and not Bytes) ?

  • kpsqlcent

    Old Hand

    Points: 385

    Useful, thank you. I'd be interested in reading the possible follow up article you mentioned.

  • sql_lock

    SSCrazy Eights

    Points: 9373

    miamikk204 (3/10/2015)


    Is the "SpaceUsed" value is in MB (and not Bytes) ?

    In my query it is referencing database_transaction_log_record_count which is "Number of log records generated in the database for the transaction."

    Nice query btw. Just added cross apply sys.dm_exec_query_plan(er.plan_handle) qp to get the execution plan 😉

  • MyDoggieJessie

    SSC-Forever

    Points: 44276

    +1 sql_lock!

    ______________________________________________________________________________
    Never argue with an idiot; Theyll drag you down to their level and beat you with experience

  • Paul Randal

    One Orange Chip

    Points: 29438

    Nice script!

    Paul Randal
    CEO, SQLskills.com: Check out SQLskills online training!
    Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
    SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
    Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005

  • remach

    SSC Rookie

    Points: 43

    sql_lock (3/10/2015)


    miamikk204 (3/10/2015)


    Is the "SpaceUsed" value is in MB (and not Bytes) ?

    In my query it is referencing database_transaction_log_record_count which is "Number of log records generated in the database for the transaction."

    Nice query btw. Just added cross apply sys.dm_exec_query_plan(er.plan_handle) qp to get the execution plan 😉

    I think that field database_transaction_log_bytes_reserved is more suitable.

    Why query

    SELECT database_id

    ,SUM(( tdt.database_transaction_log_bytes_reserved

    +tdt.database_transaction_log_bytes_reserved_system)/1024/1024)

    AS [SpaceUsed(MB)] FROM sys.dm_tran_database_transactions tdt

    GROUP BY database_id

    don't show actual size of log file?.

  • tune

    SSC Enthusiast

    Points: 139

    Thanks for the nice script Ian.:-) It is very useful

    regards

    Failure is an event not a person: Zig Ziglar

  • venkata.kolla

    SSC Rookie

    Points: 44

    Nice article good job. Please follow up with how to monitor report what caused the tlog fill up. Thanks.

    Regards

    Venkat

  • sqldba-294117

    Hall of Fame

    Points: 3486

    Nice script, Ian, Good job...............

  • Bilal Ayub

    SSC-Addicted

    Points: 411

    I would suggest to exclude tempdb as it will be showing same transaction two time, one under with tempdb.

  • swatantra_singh

    SSC Rookie

    Points: 27

    My query returns null.... what am I missing...

    Nice article for beginners If I manage to get my head around 🙂

  • Jan Lenders

    SSC Enthusiast

    Points: 139

    Excellent post; thank you.

    I have slighty altered the code by replacing DATEDIFF(MS, tat.transaction_begin_time, GETDATE()) AS [TransDuration(s)] by CONVERT(VARCHAR,DATEADD(ms,DATEDIFF(MS, tat.transaction_begin_time, GETDATE()),0),114) AS [TransDuration] and the ORDER BY [TransDuration(s)] DESC by ORDER BY tat.transaction_begin_time ASC

  • san1901

    SSC Veteran

    Points: 249

    Excellent script very useful. I am looking forward to see the monitor script in a future article. Thanks.

  • Ray Herring

    SSCertifiable

    Points: 5421

    Hi Ian,

    I like your script and I have started incorporating it into my tool kit.

    One comment on SpaceUsed. The Transaction Log Record count is certainly useful but I think the Transaction Log Bytes Used/Reserved are probably better indications of the actual space consumed. I modified the script to include those values also.

    I also think that database_transactions_begin_time in sys.dm_database_transactions might be a better choice for calculating duration, or perhaps adding it as a DB transaction duration.

    Thanks for the work.

    Ray

Viewing 15 posts - 1 through 15 (of 18 total)

You must be logged in to reply to this topic. Login to reply