patternsqlModerate
MS SQL Server DB Transaction Log Growth Rate
Viewed 0 times
logsqlratetransactiongrowthserver
Problem
I have a busy database, it has been busy for years and I've seen a transaction log size of approx a few GB a day.
In the last few weeks, that transaction log size has increased to 40-50 GB every 6 hours. This is having a big impact on my backups and the size on disk is concerning.
I'm doing a FULL backup up every other day, a log backup every 6 hours.
The log backup reduces the size of the transaction log usage as expected, however it rapidly starts increasing again.
How might I discover what is causing the relatively sudden transaction increase in transactions?
How might I further my investigations?
In the last few weeks, that transaction log size has increased to 40-50 GB every 6 hours. This is having a big impact on my backups and the size on disk is concerning.
I'm doing a FULL backup up every other day, a log backup every 6 hours.
The log backup reduces the size of the transaction log usage as expected, however it rapidly starts increasing again.
How might I discover what is causing the relatively sudden transaction increase in transactions?
- My developers are drawing blanks in terms of changes which could affect over the last few weeks
- There are no long running processes
- No scheduled jobs to perform index actions
- Redgate monitoring doesn't show any over the top writes
How might I further my investigations?
Solution
“The best time to monitor your instance was 20 years ago.
The second best time is now.”
– Chinese Proverb
I recommend enabling the Query Store. Once you start collecting data, you can either use a GUI or write a query against the sys.query_store_runtime_stats to find the Log usage heavy query.
GUI
Open the database in the Object Explorer a select the Top Resource Consuming Queries
In the top right corner, pick Configure to select which metric you're interested in
And now I can see which query used the Log the most
The details (not on the screenshot) show the plan, statements, etc.
In case you'd still not recognize the query. I would visit the Query Store DMVs and try to find the query_hash to use as a search filter for an Extended Event session.
The second best time is now.”
– Chinese Proverb
I recommend enabling the Query Store. Once you start collecting data, you can either use a GUI or write a query against the sys.query_store_runtime_stats to find the Log usage heavy query.
GUI
Open the database in the Object Explorer a select the Top Resource Consuming Queries
In the top right corner, pick Configure to select which metric you're interested in
And now I can see which query used the Log the most
The details (not on the screenshot) show the plan, statements, etc.
In case you'd still not recognize the query. I would visit the Query Store DMVs and try to find the query_hash to use as a search filter for an Extended Event session.
Context
StackExchange Database Administrators Q#317583, answer score: 10
Revisions (0)
No revisions yet.