October 9, 2023 at 5:43 am
I'm curious is there any way for me to monitor the TLog generation in my database? I am trying to pin point which sessions/SQL queries which generated huge amount of transaction log. I have alerts to notify if log space is running out, but I'm trying to find out which particular session or which query generated these logs so I can pass it over to the developers to make some changes.
October 10, 2023 at 6:10 am
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
October 10, 2023 at 11:39 am
If you're curious about transaction log activity for open transactions, Paul Randal has a script at Script: open transactions with text and plans.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
October 10, 2023 at 12:37 pm
In addition to what Johan has pointed out, you can also use Extended Events to capture log behaviors, including the query that sparked them. Here's another article by Paul Randal. NOTE: This one is using a debug event. You should 100% use extreme caution when consuming debug events. There are several other transaction log events that you can use for monitoring too. Look 'em up within the tool.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 26, 2023 at 4:56 am
Simply create an alert that reads from one of the system catalog views and notify an operator by email when the free space of the SQL Transaction Log file becomes under a predefined threshold.
sys.dm_db_log_space_usage is a dynamic management view, that is used to return space usage information for the transaction log.
I hope this helps.
DBASupport
October 26, 2023 at 4:59 am
Create an alert that reads from one of the system catalog views and notify an operator by email when the free space of the SQL Transaction Log file becomes under a predefined threshold.
sys.dm_db_log_space_usage is a dynamic management view, that is used to return space usage information for the transaction log.
DBASupport
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply