none
Transaction Logs File growing rapidly

    Question

  • Hi,

    The transaction logs file size of one of our database is growing rapidly.

    The TL file was shrunk to 2,440KB, and it went up to 261GB in about 20 days.-

    - Autogrowth is enabled
    > File Growth is set to In Megabytes: 1,024MB
    > Maximum File Size is Unlimited.

    The Initial file size (MB) for the Log file is set to 1,027.

    Questions,

    - what could be causing the log file size growing so rapidly?How can this be troubleshoot?

    -When specified the initial size for the file. This article said the size must be larger than the current file size. How do we calculate the "right" initial size when it grow from 2MB to 261GB?

    Thank you

    Friday, May 25, 2018 7:24 AM

All replies

  • - what could be causing the log file size growing so rapidly?How can this be troubleshoot?

    Hello,

    It can be a larger transaction wich fills the log, or a maintenance Task like index reorg/rebuild; do you have such?


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Friday, May 25, 2018 7:41 AM
    Moderator
  • Ray,

    Could it not be possible that the operations in SQL Server require 261 GB of transaction log space ? And please don't shrink you will make matter worse for your yourself.

    Anything starting from index rebuild/stats update/reorganize/ETL/massive data purge etc can cause log file to grow. Using snapshot, RCSI can also cause log file to grow out of proportion. Now it is your taks to find out what is causing it.

    There is nothing like "initial size" in database its a wrong terms and please don't waste time in setting value for it instead focus on finding process which is causing lot file to grow(if there is any)


    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP


    Friday, May 25, 2018 9:00 AM
    Moderator
  • Make sure that you either do regular log backups or are in simple recovery mode ("empty" the log). Then the log just need to be the size it need to be. If you generate activity worth 250 GB between empty the log file, then this is your requirements.

    Tibor Karaszi, SQL Server MVP (Web Blog)

    Monday, May 28, 2018 6:56 AM
  • Hi Olaf,

    Yes. These jobs are run on the databases (full mode):

    Job 1. Index optimize (once a day). Settings:

    EXECUTE IndexOptimize
    @Databases = 'USER_DATABASES',
    @LogToTable = 'Y',
    @SortInTempdb = 'Y',
    @UpdateStatistics = 'ALL',
    @FillFactor=20,
    @OnlyModifiedStatistics = 'Y'

    Job 2. Transaction Log backup every 15 minutes.

    Job 3: With full database backup every night > which should truncate TL?

    Wednesday, June 06, 2018 1:18 AM
  • Hi,

    The database is backup every night, both database and the TL (every 15 minutes). But this does not seem to shrink or truncate the log size.

    It has again happening ..........

    Wednesday, June 06, 2018 1:22 AM
  • Are you using any other SQL Server components (e.g. replication) that may be waiting for things to complete processing before allowing the transaction log to properly clear?

    Much of the article HERE is written for SQL 2012 and earlier but it pretty much remains valid on whichever version you have.  Start there to find the issue.


    Martin Cairney SQL Server MVP

    Wednesday, June 06, 2018 1:32 AM
  • Hi Martin,

    Yes, there are few replication jobs, but they are for other databases. there is no replication job for the database of that TL issues where logs keep growing rapidly (from 2MB to 200GB in 10 days or so).

    Wednesday, June 06, 2018 4:08 AM
  • Read through the rest of the article I sent the link for - there are a number of other things it could be and the article identifies some steps to try to identify these.

    Martin Cairney SQL Server MVP

    Wednesday, June 06, 2018 5:09 AM