Reclaiming free space on Log file.

  • One of my database has 100+ GB log file size. But it's 99% free. I like to make log file size to 20-25 GB so I can make extra free space on hard drive. The Database has Log shipping Configured. What are my best option?

    DBCC SHRINKFILE (N'AdventureWorks_Log' , 20480) ---20GB

    Is above option is good enough? If I use above, should and take full backup again? will Log shipping break? Is there any best solution with out breaking Log shipping and void taking full backup?

  • firstly satisfy yourself there is not good reason why the log file is that big. also check the growth factors and avoid percentage growth settings.

    The shrink will not affect log shipping.

    A full backup afterwards is not necessary if space and\or time is an issue, another log backup will capture the size change

    ---------------------------------------------------------------------

  • smtzac (8/19/2014)


    I like to make log file size to 20-25 GB so I can make extra free space on hard drive.

    Why?

    Do you need the space for some other database?

    Are you absolutely sure that the log doesn't need to be 100GB at some point? If it does, and you've used that space for some other database, whatever process it is that needs the log to be 100GB will fail and you'll get log full errors.

    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
  • The Log file space use always 99% free, so what's the point keeping same size?

    Yes it does, some time Transaction log file (backup from Log shipping) is 100+ GB, but one thing I am not sure about is when log is truncated by transition log backup why the Log file size stay the same? my log file size grew 20+ GB from last month (automatically) despite having log backup job every 3 hours!!

  • smtzac (8/19/2014)


    The Log file space use always 99% free, so what's the point keeping same size?

    Yes it does, some time Transaction log file (backup from Log shipping) is 100+ GB

    If you sometimes have log backups of 100 GB, then your log is not always 99% free. If you shrink the log, it's just going to grow again for those times it does need that size. Rather just leave it alone. There's no problems with a large log. There are problems from constantly shrinking/growing the log.

    Log backups will truncate the log as part of the backup. Truncate does not mean shrink. It means mark the space inside the log as reusable.

    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
  • increasing the frequency of your log backups would help control the size of the log file unless you have a single transaction producing that much log.

    ---------------------------------------------------------------------

  • smtzac (8/19/2014)


    The Log file space use always 99% free, so what's the point keeping same size?

    Yes it does, some time Transaction log file (backup from Log shipping) is 100+ GB, but one thing I am not sure about is when log is truncated by transition log backup why the Log file size stay the same? my log file size grew 20+ GB from last month (automatically) despite having log backup job every 3 hours!!

    Well does your log keep growing past that 20GB mark? There should be a relatively stable or at least predictable amount of log growth on your system.

    Unless you put a maximum size on the log file it will grow to as large as it needs to be and if it can't grow(whether the disk is out of space or it hits the max size you set) you'll start getting error messages whenever an operation needs to write to the log file.

  • Hello Mr. Gila,

    Can you please help me to understand this scenario:

    Log file size right now 100 GB

    Big translation took place, size of 125 GB.

    would the Log file size be 100+125 = 225 GB ?

    if so, what happens when I take transaction log backup after size of 225 GB?

    Would this be back to 100 GB or stay 225 GB or......?

    Thank you for your time

  • smtzac (8/19/2014)


    Hello Mr. Gila,

    Can you please help me to understand this scenario:

    Log file size right now 100 GB

    Big translation took place, size of 125 GB.

    would the Log file size be 100+125 = 225 GB ?

    if so, what happens when I take transaction log backup after size of 225 GB?

    Would this be back to 100 GB or stay 225 GB or......?

    Thank you for your time

    If your transaction required 125GB, then your log will be 125GB plus whatever other transactions that are processing.

    When you backup that 125GB transaction log, your backup will be 125GB if you are not compressing the backup. The backup of the log will then free the space inside of the transaction log but the overall size of the transaction log will remain 125GB (with 99% free or so).

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • smtzac (8/19/2014)


    Log file size right now 100 GB

    Big translation took place, size of 125 GB.

    would the Log file size be 100+125 = 225 GB ?

    Depends how much of that existing 100GB is in use. The final size of the log will be (125 + Used portion of that 100)GB

    if so, what happens when I take transaction log backup after size of 225 GB?

    Since the only thing that a log backup does is mark portions of the log as reusable, the file size after the log backup will be exactly the same as the file size before the log backup.

    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
  • Thank you SQLRNNR. sorry, I am trying to explain better way:

    There is a log file 100 GB. New transaction occurs and it shows 125 GB log file backup on E:\ (got it from Log shipping primary backup at 1 pm).

    According to Gila total log file would be 125+1 GB(99% free on 100 GB) total 126 GB.

    Next If I take a transaction log backup, will transaction log backup make log file (.ldf) smaller or just makes VLF to 0 but size remains 126 GB?

    Will there be again 99% free space on 125 GB log file (.ldf)?

    at what point I reclaim disk space? just say my .ldf file is now 500 GB but 99% free, shouldn't I claim at least 400 GB space for different purpose because .ldf is only occupied 5 GB (1%)?

  • smtzac (8/19/2014)


    Thank you SQLRNNR. sorry, I am trying to explain better way:

    There is a log file 100 GB. New transaction occurs and it shows 125 GB log file backup on E:\ (got it from Log shipping primary backup at 1 pm).

    According to Gila total log file would be 125+1 GB(99% free on 100 GB) total 126 GB.

    I think you are confusing things.

    Your log backup was 125GB. That would be the size of the transaction log that was in use. Just because the backup was 125GB, does not indicate that it was a single transaction. It very well could have been a 124GB transaction.

    Next If I take a transaction log backup, will transaction log backup make log file (.ldf) smaller or just makes VLF to 0 but size remains 126 GB?

    Will there be again 99% free space on 125 GB log file (.ldf)?

    If you take another log backup, the size of that backup could be anything from 1k to 125GB. When a log backup occurs, it does not shrink your file. It backs up the used space, and truncates that space internally. This does not reduce your VLF to 0, each vlf that was backed up and cleared is marked ready to be used by a new transaction. Your free space will be anything between 100% free and 0% free.

    at what point I reclaim disk space? just say my .ldf file is now 500 GB but 99% free, shouldn't I claim at least 400 GB space for different purpose because .ldf is only occupied 5 GB (1%)?

    You don't reclaim the disk space via log backups. The log backup reclaims the space internally for use later on.

    If the log grew to 125GB, it is likely because the log needs to be that size.

    Like Gail has already said, Shrinking the log will likely just mean it will regrow later. Constant growing and shrinking of the log is not recommended and will lead to performance issues (think slower IO for starters).

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • If the Log file grows to 100GB means it does for specific reason, so in future also It tends to grow in same fashion. So, Unless there is Urgent Requirement for Space for Other Databases don't try to Shrink the Log file. I faced similar issues in the Past in my Environment.

Viewing 13 posts - 1 through 12 (of 12 total)

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