Large transaction log backup

Sam 1,476 Reputation points
2024-06-12T05:12:43.44+00:00

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

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,318 questions
{count} votes

Accepted answer
  1. Erland Sommarskog 106.5K Reputation points
    2024-06-12T22:01:28.73+00:00

    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.

    1 person found this answer helpful.
    0 comments No comments

3 additional answers

Sort by: Most helpful
  1. LucyChenMSFT-4874 2,985 Reputation points
    2024-06-12T08:08:04.17+00:00

    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


  2. Deleted

    This answer has been deleted due to a violation of our Code of Conduct. The answer was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.


    Comments have been turned off. Learn more

  3. Deleted

    This answer has been deleted due to a violation of our Code of Conduct. The answer was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.


    Comments have been turned off. Learn more