locked
SharePoint log file too big RRS feed

  • Question

  • Okay.. so first of all I apologize if this question has been answered already, I just need to verify a few things..

    WHY IS IT THAT MY SharePoint Content log file is 50GB and one of my content DB LOG is at 17GB Both are larger than the actual DB....

    IS THIS NORMAL ?
    IF I shrink this will I mess up anything ?

    Thanks in advance.


    AJ MCTS: SP 2010 Configuration MCSA: Windows 7 If you find this post useful kindly please mark it as an answer :) TY


    • Edited by itsAboutSp Monday, December 23, 2013 1:00 PM
    Monday, December 23, 2013 12:57 PM

Answers

  • If you are going to stick with Full Recovery, make sure you're doing *Transaction Log* backups, this is what empties the TLog.

    Also, do not run Update Statistics or Reindex jobs from SQL Maintenance Plans against SharePoint databases. SharePoint has specific Health Analyzer rules (timer jobs) that do this activity for you.


    Trevor Seward

    Follow or contact me at...
      

    This post is my own opinion and does not necessarily reflect the opinion or view of Microsoft, its employees, or other MVPs.

    • Marked as answer by itsAboutSp Monday, December 30, 2013 4:07 PM
    Monday, December 23, 2013 8:29 PM

All replies

  • Its normal. Thats what Logs do. They grow. :)

    You can take log backup and then shrink. You can also change the recovery mode to simple on the DB but then look at your backup restore process first. 


    Amit

    Monday, December 23, 2013 1:07 PM
  • Shrinking wont' help at all on it's own and that is not a good sign, on the other hand it's one that can be fixed.

    If you're in Simple recovery mode then the log files should automatically empty whenever SQL thinks it's come to a sensible stopping point. If you're in Full recovery mode then the log files will continue to grow until a Full backup is taken. A second backup will then empty the logs out.

    If you need to be in the full recovery model then run two full backups. The log files will probably not reduce in size immediately and a shrink may be required. You will then need to schedule a backup schedule to prevent the log files re-growing on you.

    If you are in the simple recovery mode then you should follow the instructions above but start by re-starting the SQL service.



    Monday, December 23, 2013 1:08 PM
  • Seems perfectly normal, especially if you use a lot of versioning or transaction heavy lists within your deployment.

    Shrinking may make some difference but probably not on its own. I'd suggest that you take a back-up first.  Another possibility is setting the recovery mode simple (if not done already) but this may have implications for your disaster recovery or business continuity processes.

    Thinking forward, have you got any form of maintenance plan in place for your content databases?


    Steven Andrews
    SharePoint Business Analyst: LiveNation Entertainment
    Blog: baron72.wordpress.com
    Twitter: Follow @backpackerd00d
    My Wiki Articles: CodePlex Corner Series
    Please remember to mark your question as "answered" if this solves (or helps) your problem.


    Monday, December 23, 2013 1:12 PM
    Answerer
  • Thanks for the reply everyone.

    Just to answer some of the questions.

    We do have a DB maintenance plan that does DB_Backups (All Content and Config DBS), DB_Cleanup, DB_Reindex

    How do I backup the _log DB ? I can't seem to find this :( Please advice


    AJ MCTS: SP 2010 Configuration MCSA: Windows 7 If you find this post useful kindly please mark it as an answer :) TY

    Monday, December 23, 2013 1:27 PM
  • The log file isn't a database, it's a temporary store that hasn't been sent to the real database yet. You'll see that they are all labeled as 'Transaction log files' and the names match up to the real database files.

    This link will tell you all about transaction logs: http://technet.microsoft.com/en-us/library/ms190925.aspx

    If your backup plan is working then you will need to shrink the log files as follows: http://technet.microsoft.com/en-us/library/ms365418.aspx#ShrinkSize

    It is possible that your files grew to their stupidly large size in the past and are just full of empty space. SQL doesn't let go of space even if it's not using it, there's a good reason but that's for another thread.

    Note that the log files will, and should, increase in size after the shrink. They shouldn't be larger than your SQL databases but they can be gigabytes in size depending on the rate of change for your database.

    Check your backup logs and run a manual backup, i don't think they are working.

    PS. Re-indexing is a waste of time in 95% of SharePoint cases. Cleaup might also be wasted but i don't know the particulars.



    PPS. It doesn't look like there's been a successful backup of your Config db since early October based on the modified date.
    Monday, December 23, 2013 1:37 PM
  • I did check and it did run.

    Below are some screenshots.

    Do I need to make a backup for the transaction log ? Also..you mentioned above to make the actual DB in simple recovery mode. This step is still needed even with the backup plan ?

    Tthanks in advance


    AJ MCTS: SP 2010 Configuration MCSA: Windows 7 If you find this post useful kindly please mark it as an answer :) TY

    Monday, December 23, 2013 2:01 PM
  • if you take the config DB backup, it will include the log file as well. I would always go with DB backup incase something goes wrong.

    but for Log backup check this blog, detailed steps to take the log backusing SSMS and then shrink

    http://shareyourpoint.wordpress.com/2013/03/22/how-to-manage-large-sharepoint-configuration-database-logfile/


    Please remember to mark your question as answered &Vote helpful,if this solves/helps your problem. ****************************************************************************************** Thanks -WS MCITP(SharePoint 2010, 2013) Blog: http://wscheema.com/blog

    Monday, December 23, 2013 7:00 PM
  • There are advantages to the full backup method, however it doesn't sound like you have a dedicated DBA available.

    If that's the case then i'd just go with the simple mode as the potential benefits of the Full mode are watered down / wasted by not knowing how to use them.

    Put the databases in simple mode first. You only need to do that once per database through the database options screen. See my earlier link for more details.

    • Proposed as answer by Bob Abdelkawy Monday, October 24, 2016 8:38 AM
    Monday, December 23, 2013 7:45 PM
  • If you are going to stick with Full Recovery, make sure you're doing *Transaction Log* backups, this is what empties the TLog.

    Also, do not run Update Statistics or Reindex jobs from SQL Maintenance Plans against SharePoint databases. SharePoint has specific Health Analyzer rules (timer jobs) that do this activity for you.


    Trevor Seward

    Follow or contact me at...
      

    This post is my own opinion and does not necessarily reflect the opinion or view of Microsoft, its employees, or other MVPs.

    • Marked as answer by itsAboutSp Monday, December 30, 2013 4:07 PM
    Monday, December 23, 2013 8:29 PM
  • Trevor is right. You have another content database whose MDF file is 3+GB while the LDF file is 17GB. This means that you have not been taking transaction log backups when your database is in full recovery model. But before you change the database recovery model, you need to make sure that you do not have database mirroring/Availability Groups configured and that you have defined your recovery objectives both from the SQL Server side and the SharePoint side. Databases in full recovery model allow you to do point-in-time restores. If this is not something that you need or you are not using database mirroring/Availability Groups, you can switch the database to simple recovery model. But verify what your recovery objectives are before doing so.

    Plus, shrinking the log file does not guarantee that they will be shrunk. If there are active transactions in the LDF file, you can only shrink it up to that point in the log. The best practice is to truncate the log (in full recovery model, only a log backup will do this,) shrink the log to the smallest possible size and resize it back to a reasonable size that will not cause it to grow again. If the first attempt to shrink it does not reduce the size to the smallest possible (around 5MB,) run another transaction log backup and shrink it one more time. As an example, you can resize the content database log file to around 2GB after truncating it and shrinking it to the smallest possible size. This is to reduce the transaction log fragmentation that introduces a ton of virtual log files (VLFs.)

    Once you've managed to reduce the size of your LDF file, make sure to put regular transaction log backups in place to maintain the size and prevent this from happening again.


    Edwin Sarmiento SQL Server MVP | Microsoft Certified Master
    Blog | Twitter | LinkedIn
    SQL Server High Availability and Disaster Recover Deep Dive Course

    Database Configuration for Maximum SharePoint Performance



    Monday, December 23, 2013 11:26 PM