Shrink Database Log files (Full recovery mode)

  • Hi;

    Team; we have arround 50 databases in our SQL server 2005, all databases having full recovery mode.

    Problem is the Log file size, as datbase recovery mode is full, log file size is increasing daily (some log files size is more than 5GB sometime >200GB)

    We are taking full databses backup by using Arc srv SQL agent.

    can you please advice how can we mange Log file size of all databses, also if possible, can we automate the process to manage log file size of all databases having recovery mode as full.

  • How often are you taking log backups?

    Please read through this: http://www.sqlservercentral.com/articles/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
  • Is there any bulk transactions running on the database? Commit the transactions in small chunk and increase the tlog backup frequency may help.

    Try to narrow down when exactly issue happen? Provide more details will help you to provide the best resolution.

    ---------------------------------------------------
    "Thare are only 10 types of people in the world:
    Those who understand binary, and those who don't."

  • temp I m using following query to manage DB size (but this is not a proper solution)

    ========================================

    USE Database_name;

    GO

    – Truncate the log by changing the database recovery model to SIMPLE.

    ALTER DATABASE Database_name

    SET RECOVERY SIMPLE;

    GO

    – Shrink the truncated log file.

    DBCC SHRINKFILE (Database_name_log);

    GO

    – Reset the database recovery model.

    ALTER DATABASE Database_name

    SET RECOVERY FULL;

    GO

    =======================================

    We are taking daily full databases backup but not taking backup of transaction log, (dont want to take backup of log file).

    I have created database maintenance plan for all DB to shrink databases but it is not affecting on log size.

  • SQL_Helper (5/4/2011)


    We are taking daily full databases backup but not taking backup of transaction log, (dont want to take backup of log file).

    If you are in full recovery model, you have to back the log up on a regular basis, or it will grow until it fills the disk. In fact, the ability to back the log up (for point-in-time restores) is the entire reason for a DB being in full recovery at all.

    If you don't want the ability to restore to a point in time, then switch the DB to simple recovery model. If you do want the ability to restore the DB to point-in-time (eg exact time of failure), start taking log backups.

    Please read through the article I referenced earlier.

    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
  • If you db is in FULL recovery model then make sure log backup should be running else log will be growing like anything.and if log backup is not required then change the recovery model into SIMPLE or configure the log backup as per your log file growth duration and keep recovery as FULL.

    http://manvendradeosingh.blogspot.com/

  • You can't do both. If your database is in Full recovery, you have to run log backups. If you don't want to run log backups, you take the database to Simple Recovery. But, in so doing you lose the ability to perform a point in time recovery. I wrote a blog post[/url] on this because the question comes up so often.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • Team thanks;

    1- I have configured daily backup of full database and transaction log of all databases

    2- Recovery mode is still full

    Now please help on following

    * as now I m taking daily full backup of database and transaction logs, will it reduce the log size auto or need perform some extra tasks to maintain log file size after bakcup?

    * if I select recovery mode as simple for some databses, how can we manage log size?

  • Daily is not enough for log backups. The interval between log backups should be the maximum amount of data you are willing to lose in a disaster.

    Log backups are generally done hourly, every 30 min, ever 15 min or even more frequently.

    You should do a once off shrink of the log file (and only the log file) to a reasonable size (not 1 MB) and then leave it alone.

    In simple recovery the log will automatically be reused. Again, do a once-off shrink to a reasonable size and leave it alone.

    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
  • Whether in Simple or in Full, you need enough space in your log to support the transactions you put through it. Exactly what that size represents is something that you need to determine at your end. You want it to be sized a bit large just to take into account that you may hit a spike in activity or someone may save an extra large set of data. Other than that, in general, you should be able to set it to a particular size and then leave it alone. If you find it grows one day, leave it at the larger size. You have to have enough space.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • What do you mean by once-off?

  • What do you mean by once-off?

  • Exactly that. Once.

    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
  • Thanks for quick reply. Does it matter if I shrink at peak or off-peak hour?

  • If you don't care about degrading performance for your users, shrink at peak time.

    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

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

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