9

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?

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

10
  • What is the result of select name, log_reuse_wait_desc, is_auto_shrink_on from sys.databases? Is there any long running process, check using DBCC OPENTRAN? Something must be making the log grow again, so it's either a scheduled task or a long running process. Sep 29, 2022 at 11:07
  • You have Redgate monitoring in place, did you notice increase in Batch Requests/sec metric from when you had few GB per day, and what it is now ( when its 40-50 GB per day) ? Sep 29, 2022 at 11:10
  • Also, did you notice increase in Transactions/sec Write Transactions/sec Active Transactions/sec for that database ? What it was back then, and what it is now ? Sep 29, 2022 at 11:11
  • @Charlieface log reuse wait returns "LOG_BACKUP" and 0 No active open transactions. when running OPENTRAN
    – Damo
    Sep 29, 2022 at 11:16
  • 2
    I know this doesn't answer your question, but it may solve your problem...Any reason you can't increase the frequency of which you're taking Transaction Log backups? Either your database is exhibiting a lot more transactions now than it did before, or someone / something is running one huge transaction. If it's the former, then increasing your Log backup frequency will mark the Log for re-use more frequently, minimizing growth. It'll also allow you a more granular recovery time period than 6 hour increments. (A lot of people usually back it up multiple times an hour - I do every 5 minutes.)
    – J.D.
    Sep 29, 2022 at 12:00

2 Answers 2

10

“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

enter image description here

In the top right corner, pick Configure to select which metric you're interested in

enter image description here

enter image description here

And now I can see which query used the Log the most

enter image description here

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.

1
  • 2
    This is actually really helpful, and not something I was aware of with Query Store, nice!
    – J.D.
    Sep 30, 2022 at 1:16
4

How might I further my investigations?

Given that you have no scheduled jobs to perform index actions, and no long running processes / transactions - next thing you could check is a number of Logical Writes, and queries that are doing these writes.

It can be done following ways:

  • In Redgate Monitor - select "Totals", sort queries by totals of "Logical Writes". Check out queries that make writes against tables in your database in question.

  • Get the sp_BlitzCache. Run exec sp_BlitzCache @SortOrder = 'Writes'

  • Use the SQL Server Audit feature, create an audit that would log INSERT UPDATE and DELETE operations against that database. Possibly add BULK INSERT and CREATE TABLE in there. Review these queries

  • Extended Events

If you notice any of queries that are doing excessive writes against tables in that database (1 logical write = 8 KB of written data), see how you can reduce number of logical writes. If that is possible in your circumstances, of course

Also, good thing would be to monitor next metrics on a regular basis

Batch Requests / sec
Transactions / sec
Write Transactions / sec
Active Transactions / sec

That way you will know what are your baselines, and when your workload changes significantly

1
  • 2
    thank you. While I accepted the other answer as it gave me a smoking gun to aim at, your contribution also very much helped. Sorry I couldn't accept both.
    – Damo
    Sep 29, 2022 at 16:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.