[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 231
  • Last Modified:

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.
0
gopher_49
Asked:
gopher_49
1 Solution
 
Jayaraja JayaramanCommented:
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
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
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
 
gopher_49Author Commented:
Ended up changing DB to simple mode.. shrinking.. And putting back to full recovery mode.
0

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now