SQL Transaction Log

  • Hi everyone,

    My transaction log file is bigger and bigger every day, can I truncate the file? If it can, does it affect any thing such as performance?

    Best regards;

  • What is the recovery model of the database? If it is FULL then consider scheduling a transaction log backup to manage the transaction log.

    Kurt

    Kurt W. Zimmerman
    SR DBA
    Lefrak Organization
    New York, NY

    http://www.linkedin.com/in/kurtwzimmerman

  • Hi,

    Thank you, it is the full recovery mode. I already configured the schedule to back up transaction log. Could you please clarify why do we need to configure the schedule for this backup?

    Best regards;

  • How often do you back up your transaction log? I have t-log backups that runs on frequencies of from 15 minutes to 1 hour.

    Kurt

    Kurt W. Zimmerman
    SR DBA
    Lefrak Organization
    New York, NY

    http://www.linkedin.com/in/kurtwzimmerman

  • Please take a read through this: http://www.sqlservercentral.com/articles/Administration/64582/

    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
  • Hi,

    Thank you, it is configured for every 3 hours. Sorry could you please explain what are differences between configuring the schedule to back up transaction log and truncate t-log?

    Best regards;

  • You probably don't have time right this second, but this book (SQL Server Backup & Restore)[/url] is very good, and the pdf version can be downloaded for free.

    The reason people are asking how frequently you back up the log file is that backing it up helps to control the growth.

  • jason123 (10/3/2013)


    Sorry could you please explain what are differences between configuring the schedule to back up transaction log and truncate t-log?

    That's in the article I referenced.

    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
  • jason123 (10/3/2013)


    Hi everyone,

    My transaction log file is bigger and bigger every day, can I truncate the file? If it can, does it affect any thing such as performance?

    Best regards;

    Hi,

    You should do backups of your Transaction log on time intervals usually somewhere between 10-60 minutes. This depends on other factors as well. Additionally your TLog autogrowth is important for the virtual log files inside, which number is given with the following:

    Growth No. VLFs

    Chunks <= 64MB 4

    Chunks in range (64MB - 1GB] 8

    Chunks > 1GB 16

    DBCC LOGINFO(<DB_NAME>) will give you the number of VLFs. You usually keep this number as possible as lower (again depends on the system).

    For detailed analysis of the TLog just search this site, or google and you'll find a lot of stuff.

    Regards,

    IgorMi

    Igor Micev,My blog: www.igormicev.com

  • GilaMonster (10/3/2013)


    Please take a read through this: http://www.sqlservercentral.com/articles/Administration/64582/

    I agree, Gail's article has all of the details you need to know about managing transaction logs. Now if you are experiencing an unusual growth or rapid growth in your transaction logs, if I were you, look to see what has been added or is new.

    If an increased level of transactions are hitting your database then you may want to rethink your transaction log backup strategy so you have not only better management of the transaction log but also have a more granular recovery model in the event something were to go belly up.

    Kurt

    Kurt W. Zimmerman
    SR DBA
    Lefrak Organization
    New York, NY

    http://www.linkedin.com/in/kurtwzimmerman

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

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