0

I'm following up on this Why is my SQL Log File Huge? article.

Looking at the disk usage report, the transaction log space use is practically zero. It shows 99.1% unused.

It is schedule to backup the log every hour for 24hours/day. Every hourly trn file is quite small but there is one backup at round 2AM that is close to the actual database data file size.

If I schedule the hourly log backup from 3AM to the next 1AM, then the next big trn file is 4AM.

If I scheduled the log backup from 4AM to 12AM then the next large trn file is 5AM.

If I schedule the hourly log backup from 12AM to 11:59:59PM then the next large trn file is 2AM.

The pattern seems to be that whatever the first of the next day backup, that trn file will be large.

The recovery mode is set to full. The log is set to 5MB autogrowth and max out at 2TB. I've also tried the Monitoring SQL Server database transaction log space to track the log space used at every hour. At every hour the log spaced used was never bigger than 99MB and most are .8MB in size.

Help is much appreciated!

Improve this question
2
  • 1
    5MB is way too small of a growth amount - I'd go no smaller than 512MB-4GB in most cases. You must have some sort of large operation running every night that is generating this log - maybe over zealous index maintenance? You can also make t-log backups happen more frequently. But you haven't said why a large transaction log is a problem yet...
    – LowlyDBA - John M
    Apr 30, 2020 at 17:50
  • 2
    Do you need point-in-time recovery? Almost sounds like this is a reporting database that gets reloaded every night. If the data isn't changing during the day, there's no need for full recovery mode--put it in simple mode and run a full backup after the nightly data load (if that's what is happening).
    – Tony Hinkle
    Apr 30, 2020 at 19:05

1 Answer 1

Reset to default
2

It's probable that you had a long running query that caused a bunch of transactions. Something like a large batch of inserts/deletes/updates. You can track these down in the query store if you have a recent version of SQL Server, or you can query the plan cache for long running transactions.

Once you've found the query, you can figure out what it's doing and go from there. You may just be stuck with it, but finding out what's causing the large amount of transactions is your first step.

Also, you should be aware that an autogrowth of 5MB is extremely small, and can result in a lot of Virtual Log Files when it grows, which can hinder startup performance. I suggest looking into tuning the growth rate as well.

https://www.brentozar.com/blitz/high-virtual-log-file-vlf-count/

Improve this answer
1
  • I found the plan cache of none SELECT query but it's about 2145 query. Of these 2145 query, is there a faster/better way of finding which query had a long running?
    – 2myCharlie
    May 1, 2020 at 17:52

Your Answer

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

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