Large transaction log backup

Question

Hi All.

From last 4-5 days we are seeing log backup file sizes increased from 500 MB's to 100GB files.

What does it indicate and what should we checking at? the queries which are running during those times? This is not just happening during weekends (index rebuilds ..) but during weekdays also its the same behavior.

We take log backups every 30 mins.

Regards,

Sam

Answer 1

Apparently there is something which is writing a lot to the database. Maybe someone found it a good idea to rebuild indexes all days of the week. Maybe there is a change in the use of the application.

Do you have Query Store enabled? In such case you could dig there for queries with a large amount of writes.

Answer 2

Hi @Sam,

Thank you for reaching out and welcome to Microsoft Q&A.

• Could you please tell me do you enable Change Data Capture on a database? On a CDC-enabled database, capture job latency holds up log truncation to ensure changes can be captured from the transaction log to the CDC change tables, preventing loss of change data. You can use Transact-SQL (T-SQL) to specify the transaction log threshold and the time interval to monitor the transaction log.
• Rebuilding indexes will take a toll on your transaction log file, Large-scale index operations can generate large data loads that can cause the transaction log to fill quickly. Here is a known issue, hope the answers and links they provided can help you well!
• Maybe you set wrong database auto growth?

Feel free to share your issue here if you have any confusions. Your time and cooperation are much valued by us. We are looking forward to hearing from you to assist further.

Best regards,

Lucy Chen

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

https://docs.microsoft.com/en-us/answers/support/email-notifications