What SQL Statements Are Currently Using The Transaction Logs?

Question

Post reply

What SQL Statements Are Currently Using The Transaction Logs?

ianstirk

Ten Centuries

Points: 1310
More actions
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?

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

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

miamikk204 SSC Veteran Points: 201 More actions · Answer 1

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

kpsqlcent Old Hand Points: 385 More actions · Answer 2

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

sql_lock SSCrazy Eights Points: 9373 More actions · Answer 3

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: 44486 More actions · Answer 4

+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: 29544 More actions · Answer 5

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 More actions · Answer 6

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 More actions · Answer 7

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

regards

Failure is an event not a person: Zig Ziglar

venkata.kolla Valued Member Points: 51 More actions · Answer 8

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 More actions · Answer 9

sqldba-294117

Hall of Fame

Points: 3486

March 16, 2015 at 7:15 am

#1784244

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

Bilal Ayub SSC-Addicted Points: 411 More actions · Answer 10

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 More actions · Answer 11

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 More actions · Answer 12

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 Old Hand Points: 359 More actions · Answer 13

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

Ray Herring SSCertifiable Points: 5533 More actions · Answer 14

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