Post reply

Tlog growth analysis

  • July 10, 2011 at 10:38 pm

    #254388

    Hi All,

    I was in little dilama on this concept and how to exactly react to such situations.

    Very often clients will be coming and asking us the txn log is growing very rapidly. Mostly when sharepoint content database are concerned.

    Sometimes the tlogs will grow up to 33 Gigs and may also next day it is growing 67 gigs.

    I came to know a crawling job is being run and it is taking 7 hours and sometimes it keeps on running.

    normally full crawling should take 2-3 hours and incremental crawling would take 10-15 mins.

    But we have observed that incremental itself it is taking 7 hours and never ended.

    finally we had look into log_reuse_wait_desc column in sysdatabases and had to take a log backup

    and managed to shrink tlog to 1 gigs.

    But i wanted to know how to react in such situations and what are preemptive actions can be taken

    go forward.

    Basically i was not able to know why the tlog has grown such drastically.

    In such situations how should we troubelshoot the issue and figure out the reason for the tlog growth.

    How to monitor the activities on a particular database and casuing the growth of tlog and finally we should be able to figure out which one is the actual culprit.

    I know this is something which is environment specific but am looking for some generosu answers who can help me out in handling such situations.

    What are all the possible solutions we can look for and how to capture all information and put forward the management and answer bcz of this, the TLOg has been growing rapidly ??

    Thanks in advance.

  • July 11, 2011 at 2:59 am

    #1350936

    There is number of things a transaction log growth.As you told, is it a content-sharepoint database only growing daily? or anyother DB's like custom..

    1.Have you running t-log backup? How often is it?

    2.DO you have any rebuild job in place your own or default one?

    3.what it shows 'log_reuse_wait_desc'

    Do monitor your server if you want set up the baseline like the t-log grows 10 GB then you'll get an email.

    For more you want run a server side trace and capture the data and find where the modification/transaction running huge.

    Muthukkumaran Kaliyamoorthy
    https://www.sqlserverblogforum.com/

  • July 11, 2011 at 7:32 am

    #1351065

    Thanks Muthu.

  • July 25, 2011 at 7:58 pm

    #1358222

    Hi

    It also depends on the recovery model of your databases when the log will get truncated.Lol @ name

    Cheers

    Jannie

Viewing 4 posts - 1 through 3 (of 3 total)

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