ldf file size

  • Dear All

    I have a db for which recovory model is Full. And i am taking only full back up.

    .Ldf size is bigger than the .mdf file. I wanted to reduce the file size. On one site i saw following solution

    1. change the recovery model to simple

    2. change the ldf initial file size to 1mb

    3.Change the recovery model back to Full.

    Is it a correct way? What will be the side effects of this?

    Regards

    Krishna1

  • No, it is certainly not the right way to do this. What you should do is take LOG backups every now or so often in between your full backups. Depending on what your situation is, meaning, how many database changes happen over what period, what your point-in-time restore requirements are in case of failure will influence the appropriate intervals between LOG backups. Also, is this is a High Availablity scenario, i.e. are you doing log shipping or something similar? It might be overkill to be in Full Recovery in the first place.

    --------------------------------------------------------------------------
    A little knowledge is a dangerous thing (Alexander Pope)
    In order for us to help you as efficiently as possible, please read this before posting (courtesy of Jeff Moden)[/url]

  • Hi,

    The transaction log has to be configured well too. Growing files is pretty expensive operation.

    Do you care about the VLFs count? The following link can help you configure it well - http://www.sqlskills.com/blogs/kimberly/transaction-log-vlfs-too-many-or-too-few/

    Check the following link too: http://www.sqlskills.com/blogs/kimberly/8-steps-to-better-transaction-log-throughput/

    Regards,

    Igor

    Igor Micev,My blog: www.igormicev.com

  • No. Completely incorrect approach.

    Please 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
  • The point of confusion here may be the recovery model set to "Full". When the recovery model is set to full, that means you are taking full backups, (Probably differential backups), and -here's the important part- regular log backups. These are *required* for the Full recovery model.

    If you use the "Simple" recovery model, you are indicating to SQL server that you will be taking full backups from time to time.

    In simple recovery mode, when you take a full backup, I believe the log file is backed up and truncated.

  • ryan.mcatee (6/18/2013)


    In simple recovery mode, when you take a full backup, I believe the log file is backed up and truncated.

    No.

    In simple recovery model a checkpoint will truncate the log. Checkpoints run on a regular basis. In full and bulk-logged recovery model a log backup truncates the log

    Full backups do not, in any recovery model, truncate the log. Nor do they back up the log (though they include enough of the log for a consistent restore in all recovery models)

    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
  • Okay, then, the SQL geniuses here have debunked my understanding of backup models, so ignore everything after my first paragraph. 😛

  • Details of recovery models:

    http://www.sqlservercentral.com/articles/Administration/75461/

    http://www.sqlservercentral.com/articles/Recovery+Model/89664/

    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
  • Please read through the backup docs more carefully because your understanding of Recovery Models is completely wrong!

    The Full model maintains a transaction log so that point in time recoveries are allowed. For this you need to backup your database appropriately including a minimum of a full backup and associated transaction logs. On being backed-up the amount of data contained within the log will be reduced although the file size remains the same to the file system.

    In Simple mode, the transaction log is only maintained (for simplicity) for the purpose of being able to rollback transactions (as a singular example). Point in time recoveries are not possible under this mode.

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

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