March 9, 2015 at 9:24 pm
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
Is the "SpaceUsed" value is in MB (and not Bytes) ?
March 10, 2015 at 3:02 am
Useful, thank you. I'd be interested in reading the possible follow up article you mentioned.
March 10, 2015 at 3:32 am
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
+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
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
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
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
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
Nice script, Ian, Good job...............
June 16, 2016 at 10:17 pm
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
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
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
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
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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy