MS SQL SharePoint 2007 LOG file grown to be huge

Posted on 2014-08-14
Last Modified: 2014-09-07
I have a SharePoint 2007 LOG file that has grown to 147 GBs...  The DATA file is only 46 GBs..  I have a current DATA and LOG backup.  What can I do to shrink this?  I already tried releasing unused space.  That only resulted in 500 MBs less.
Question by:gopher_49
    LVL 8

    Expert Comment

    by:Jayaraja Jayaraman
    you can use DBCC shrink to shrink the log file.simply
    DBCC SHRINKFILE (AdventureWorks2012_Log, 1);
    LVL 22

    Expert Comment

    by:Steve Wales
    That is not necessarily true - or at least, it's only part of the solution.

    My guess is that you're running in Full Recovery mode.

    Have a read of this article that explains why your log file grew and how to shrink it:

    In short:
    1) Backup your log files
    2) Then shrink

    If you don't have a spare 147 GB free to backup the logs, you could switch the recovery mode to simple and truncate the log - but you lose your point in time recovery option by doing that.

    I don't know enough about sharepoint to know WHY they would recommend it, but Microsoft even recommends to run your Sharepoint database in simple recovery mode in order to restrict the growth of your log file:

    Personally, I'd just schedule log backups and be done with it, unless there's some reason that can't be done.

    It should be noted that right after they say "run in simple" they say "backup your log frequently" (which you can't do in simple recovery mode).

    Gotta love self contradicting documentation!

    However, once you've got your shrink done, either way will stop this from happening again, just make sure you have disaster recovery planning complete and in place.

    Author Comment

    I'm running the DB in full recovery mode..  6 days a week a LOG backup runs and one day a week a DATA runs.  But..  We noticed the LOG file growing and not reducing.  I think it's been going on for a while and we just now noticed it.  We tried shrinking the LOG file but only 500 MBs was available to shrink.  

    So..  Since I'm running DATA and LOG jobs.. And over 7 days have been successful...  And... Since we already shrunk the LOG file (only had 500 MBs of unused space) I guess we now need to regrow the LOG file size via the LOG file settings in SQL Management Studio?
    LVL 22

    Expert Comment

    by:Steve Wales
    How often are you backing up your logs ?  Once a day ?

    Try doing them more frequently.  Then right before your shrink, run two of them back to back - I don't know WHY, but I've found that when I need to shrink a log file, I run one backup, try to shrink don't get very much back.  Run two backups one after the other (log backups, not full backup), try the shrink again and I reclaim huge amounts of space.
    LVL 35

    Expert Comment

    by:David Todd

    The standard database backup routine (if there ever was such a thing) was full backup every night. If in full recovery mode then a transaction log backup every hour.

    By doing the log backup approx. 24 times more often than you are currently doing, then you could expect the transaction log to stay at around 5% of current.

    If its too much effort/takes too long/file is too big/ to take a full backup each night, then I suggest that you take a diff backup every night. Then the restore process is last full backup, last diff backup, log backups from then on in sequence.


    Accepted Solution

    I ended up have to change the DB to simple mode.. Shrink the files and then move back to full recovery.  That reduced the LOG from over 100 GB's to 10 GB's.

    Author Closing Comment

    Ended up changing DB to simple mode.. shrinking.. And putting back to full recovery mode.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
    In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
    This video is in connection to the article "The case of a missing mobile phone (". It will help one to understand clearly the steps to track a lost android phone.
    This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor ( If you're looking for how to monitor bandwidth using netflow or packet s…

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now