Post reply

Regarding LDF file.

  • March 16, 2009 at 8:45 am

    #219160

    Dear All,

    LDF file of our database has grown much and I need to clear the same. How can I clear the LDF file.?.I take full Backup Regulerly.

    Please Guide.

    Thanks in advance.

  • March 16, 2009 at 8:57 am

    #960293

    You should schedule transaction log back-ups this will truncate the log and mark the space for re-use.

    Your database needs to be on full-reovery mode to allow this.

  • March 16, 2009 at 9:11 am

    #960316

    Yes...have done that...but size doesn't reduce..It shows me the same size

  • March 16, 2009 at 9:17 am

    #960329

    swmsan (3/16/2009)

    Yes...have done that...but size doesn't reduce..It shows me the same size

    It won't. Log backups make the space within the file available for reuse. They don't decrease the file size. Don't worry about the file size, there's no penalty for a large log file and if the DB does use it all at some time then it is necessary that it's that size.

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

    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

  • March 16, 2009 at 11:31 pm

    #960799

    Thanks...

    But the problem is that there's no space now. Hence manager asked me to reduce the log size..pls guide what to do now..?

    shall I truncate it ..?

  • March 16, 2009 at 11:45 pm

    #960808

    Since you have performed a log backup,you'll have free space in the log file.Now shrink it.You'll get space released to the disk.

    Never truncate it....it may lose uncommitted transactions by doing so.

  • March 17, 2009 at 1:02 am

    #960833

    SQL Reddy (3/16/2009)

    Since you have performed a log backup,you'll have free space in the log file.Now shrink it.You'll get space released to the disk.

    Yup. However if you have not resolved whatever cause the log to grow, it will just grow again, and repeated shrink/grow operations are a bad idea. They cause file-level fragmentation and can result in lots of Virtual log files that slow down backups.

    You need to work out how big the log needs to be for your operations and the frequency of your log backups. If that's too big, increase the frequency of the log backups

    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

  • March 17, 2009 at 5:22 pm

    #961497

    swmsan (3/16/2009)

    Thanks...

    But the problem is that there's no space now. Hence manager asked me to reduce the log size..pls guide what to do now..?

    shall I truncate it ..?

    You can definitely do so. But, you should start root cause analysis and research why your log is getting so huge? Any open transactions? Any maintenance tasks thats causing it to grow? Start doing it and next time you would not be in this situation again.

  • March 18, 2009 at 7:33 am

    #961850

    if you guys dont take Transaction log backups, you can change recovery mode of database to simple so that the transactions will be deleted in timelt manner...

    make sure you guys normally dont have or take transaction log backups as part of your maintenance plan.

  • March 18, 2009 at 7:46 am

    #961872

    you can refer this link:-

    http://qa.sqlservercentral.com/articles/Transaction+Log/65877/

  • March 18, 2009 at 11:30 pm

    #962486

    Thanks..

    Will browse through the link.

  • March 19, 2009 at 8:14 am

    #962722

    So shrinking the log file has no side effects? Especially on performance

    Because I want to shrink it but I'm affraid of unknown side effects.

    I know that shrinking the database has serious effect on performance...so why is it different on the log? because information are olny introduced in the log and rarely used?

  • March 19, 2009 at 8:23 am

    #962729

    From the performance side, as far as I know shrink will cause page split and thus causing fragmentation on indexes.. if you really dont want to shrink it then dont shrink it..

  • March 19, 2009 at 8:32 am

    #962740

    shnex (3/19/2009)

    So shrinking the log file has no side effects? Especially on performance

    Because I want to shrink it but I'm affraid of unknown side effects.

    I know that shrinking the database has serious effect on performance...so why is it different on the log? because information are olny introduced in the log and rarely used?

    Why did you chose to shrink the log files? In simple terms, If the log file is growing then it means that it needs that amount of space. Shrinking the log file does have impact on performance. Instead think of taking regular log backups.

  • March 19, 2009 at 8:41 am

    #962752

    If I make log backups ...the log space will be reusable, but in order to use the log information I will have to restore the log backups I think...am I wrong?I haven't worked with logs too much, so I'm curious...

    From my point of view....the difference is not so big....maybe I'm wrong

Viewing 15 posts - 1 through 15 (of 19 total)

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