Post reply

Transaction log file is gowing anf growing

  • October 4, 2012 at 3:58 pm

    #393318

    Hello,

    I am not a DBA but I am doing some DBA jobs. We have a database with full recovery mode, so the transaction log is keeping growing and growing.

    I have read some articles, saying we can backup transaction log file to limit tansaction log file growing too big, and we never shrink or truncate the file, ect. Please advise details regarding transaction log file.

    Thanks

  • October 4, 2012 at 4:19 pm

    #1545845

    Perform a transaction log backup. While this will not shrink the log file itself (though that is sometimes possible to do, depending on the circumstances), it will allow SQL Server to re-use the space allocated, hence ceasing the growth of the file.

    Sample below (assuming databasename of ABC123:

    backup log [ABC123] to disk=N'C:\Database Backups\ABC123.bak' WITH NoFormat, NoInit, Name=N'ABC123 Transaction Log Backup'

    Please note that you will need to ensure that the volume and directory for creating the backups (C:\Database Backups\, in this case) is created and has space.

  • October 4, 2012 at 6:26 pm

    #1545865

    thank you.

    I will practice it on a test database first. Do I have to schedule a job to do the transaction log backup many times or just one time, if many times, how often? After backup, do I have to do other things? I appreciate your time.

  • October 4, 2012 at 6:31 pm

    #1545867

    You will need to continuously backup the transaction log, it's part of maintaining a database in anything except simple recovery. Most places I know do either 1 hour or 15 minute backups. You'll want to schedule a job to do this for you and have some kind of archive process put in place.

    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
    Twitter: @AnyWayDBA

  • October 5, 2012 at 1:32 am

    #1545915

    Please read through these - Managing Transaction Logs[/url]

    http://www.sqlservercentral.com/articles/Transaction+Log/72488/

    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

  • October 5, 2012 at 11:02 am

    #1546221

    How often you take transaction log backup?

  • October 5, 2012 at 2:41 pm

    #1546319

    deleted - misread context of question.

Viewing 7 posts - 1 through 6 (of 6 total)

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