Post reply

What SQL Statements Are Currently Using The Transaction Logs?

  • March 9, 2015 at 9:24 pm

    #301934

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

  • March 10, 2015 at 1:43 am

    #1782781

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

  • March 10, 2015 at 3:02 am

    #1782803

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

  • March 10, 2015 at 3:32 am

    #1782811

    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 😉

  • March 10, 2015 at 12:04 pm

    #1783045

    +1 sql_lock!

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

  • March 10, 2015 at 4:12 pm

    #1783089

    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

  • March 12, 2015 at 1:58 am

    #1783520

    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?.

  • March 13, 2015 at 1:37 am

    #1783780

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

    regards

    Failure is an event not a person: Zig Ziglar

  • March 14, 2015 at 12:46 pm

    #1784055

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

    Regards

    Venkat

  • March 16, 2015 at 7:15 am

    #1784244

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

  • June 16, 2016 at 10:17 pm

    #1885532

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

  • June 17, 2016 at 2:23 am

    #1885550

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

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

  • June 17, 2016 at 3:17 am

    #1885555

    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

  • June 17, 2016 at 6:33 am

    #1885578

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

  • June 21, 2016 at 11:41 am

    #1886094

    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 17 total)

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