Post reply

Transaction Log Growth

  • April 8, 2013 at 2:35 pm

    #394542

    How to find the transaction/process/Job which cause growth of transaction log size?

    Thanks

  • April 8, 2013 at 2:45 pm

    #1604569

    Care to elaborate? Your question is vague and open to many answers.

  • April 8, 2013 at 2:54 pm

    #1604572

    Is there a way to find the cause of transaction log size growth?

    Let me know if its still unclear?

    Thanks

  • April 8, 2013 at 2:59 pm

    #1604574

    Every process doing a modification will increase the log size. If you mean the transaction log is not truncating even if you do a backup or if the database is in simple recovery mode, it can be because of many different reasons.

    You can do a query on sys.databases view and look at log_reuse_wait column to find out why the log is not truncating. If it is because of a open transaction , you can try to find the culprit be looking at DBCC OPENTRAN.

  • April 8, 2013 at 3:02 pm

    #1604575

    Still unclear. Since all changes to a database are logged, the transaction log is going to grow if there is insufficient space to hold the active transactions.

  • April 8, 2013 at 3:49 pm

    #1604581

    Recovery model is FULL and Log backup happens every 15mins.

    Let me know if you need more details

    Thanks

  • April 8, 2013 at 4:21 pm

    #1604594

    Maybe take a read through this: http://www.sqlservercentral.com/articles/Transaction+Log/72488/

    As for what's causing the log to grow, well, first you have to identify why it's growing. See referenced article. Once you know what the cause is, then you can start looking for culprits. As it is, there's no way to answer your question because we don't know what's preventing log reuse.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • April 9, 2013 at 1:23 am

    #1604693

    Admingod (4/8/2013)

    Recovery model is FULL and Log backup happens every 15mins.

    Let me know if you need more details

    Thanks

    Compare the period of transaction log backups and see which time frame the log backup size is large. Focus on that period. Try to run a server side trace to see what is running in the server.

    These are only guess!!. Only you can find out what is happening in the server to make the log to grow out of proposition.

  • April 9, 2013 at 4:39 am

    #1604751

    If you have a trace running, maybe you can match up big t-log backup files with events in the trace file for the same time period.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply