locked
How to shrink the sharepoint database log file RRS feed

  • Question

  • Hello,

    I have a SharePoint content database with the size 70GB but he log file is 450GB. i wanted to shrink the log file. Can anyone tell me  how to do that ? It would be great if anyone can provide me the complete flow.

    1. Do I have to shink the log file when the database is offline?
    2. what if we just take the backup and create a new log file ?
    3. How do we restrict the size of the log file by not growing big but just auto shrink by it self.

    This is all on the production server, i need to fix this asap. Any help would be appreciated.

    Thanks in Advance.

    Friday, September 24, 2010 2:02 AM

Answers

  • Hi Rebel537,

    I suggest that you read this white paper "Database maintenance for Office SharePoint Server 2007 (white paper)". It contains information and procedure to measure and reduce fragmentation and shrink Sharepoint database.

    Hope that helps you, Mario


    Mon Blog/My Blog (in french) LinkedIn
    Friday, September 24, 2010 10:42 AM
  • Hi,

    I think you can shrink the log file when database is offline.

    Probably your database are set to full recovery model and you're not taking transactionlog backups. If you transaction logs (the LDF files) are getting large that means one thing. Your recoery mode is set to Full. You can change your database recoery mode to "Simple" and transaction logs won't get so large.

    For the most part, I never, ever recommend shrinking database files or transaction logs. Give them space to grow. That's what databases do, they get more data and they get larger, Every time you make SQL expand a database or transaction log you impact performance.

    I wish you every success in your important work.

    Best regards,

    David Hu

    • Marked as answer by Mike Walsh FIN Thursday, February 24, 2011 6:30 PM
    Tuesday, September 28, 2010 1:39 AM

All replies

  • Set the backup model to simple. Back the database up and the log should truncate.
    Hope that helps.
    SharePointNinja

    Friday, September 24, 2010 4:46 AM
  • Hi Rebel537,

    I suggest that you read this white paper "Database maintenance for Office SharePoint Server 2007 (white paper)". It contains information and procedure to measure and reduce fragmentation and shrink Sharepoint database.

    Hope that helps you, Mario


    Mon Blog/My Blog (in french) LinkedIn
    Friday, September 24, 2010 10:42 AM
  • You can not automatically shrink the log files if the database is not in simple recovery mode.  so what you would need to do to keep the log file from growing is write a script or console app to change the database recovery model to simple, backup the database and shrink the log file.
    Friday, September 24, 2010 1:56 PM
  • Hi,

    I think you can shrink the log file when database is offline.

    Probably your database are set to full recovery model and you're not taking transactionlog backups. If you transaction logs (the LDF files) are getting large that means one thing. Your recoery mode is set to Full. You can change your database recoery mode to "Simple" and transaction logs won't get so large.

    For the most part, I never, ever recommend shrinking database files or transaction logs. Give them space to grow. That's what databases do, they get more data and they get larger, Every time you make SQL expand a database or transaction log you impact performance.

    I wish you every success in your important work.

    Best regards,

    David Hu

    • Marked as answer by Mike Walsh FIN Thursday, February 24, 2011 6:30 PM
    Tuesday, September 28, 2010 1:39 AM
  • Thank you Dave !! I was succesful in doing that the lat time and now i again have the same problem I restricted the file growth to 200 GB the last time and now it is 180 GB. I think i need to follow the same procedure again and see how it goes this time.

    Thank you so much for your help.

    Thursday, February 24, 2011 6:02 PM