MS SQL SharePoint 2007 LOG file grown to be huge

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.
gopher_49Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jayaraja JayaramanSharepoint Architect ConsultantCommented:
you can use DBCC shrink to shrink the log file.simply
DBCC SHRINKFILE (AdventureWorks2012_Log, 1);
GO
0
Steve WalesSenior Database AdministratorCommented:
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:  http://www.experts-exchange.com/Database/MS-SQL-Server/A_11077-How-to-shrink-a-bloated-log-file.html

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:  http://technet.microsoft.com/en-us/library/cc678868%28v=office.15%29.aspx

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.
0
gopher_49Author Commented:
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?
0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

Steve WalesSenior Database AdministratorCommented:
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.
0
David ToddSenior DBACommented:
Hi,

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.

HTH
  David
0
gopher_49Author Commented:
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.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
gopher_49Author Commented:
Ended up changing DB to simple mode.. shrinking.. And putting back to full recovery mode.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.