SharePoint - Database Log files are too big

Asked By Ashish kanoongo

13-Jun-07 01:20 PM

I am using MOSS 2007 with SQL 2000. I am having an issue on my  server. The
system disk is becoming full in every 2 week. I noticed that my sql server
ldf (log files) is very big specially wss-server-content database logfile it
is about 7 GB and there are other 2-3 databases, their log file sizes are
approx 3 gb. I reviewd in sql server enterprice manager and check the

Is this recommended if I fix the growth to 2 MB, should I loss any data
here? If I shrink the databse, again it is on going process everytimg I need
to shrink the databse.

Please suggest what should I do?

Ashish

13-Jun-07 04:02 PM

If your transaction logs (the LDF files) are getting large that means one
thing.  Your recovery mode is set to Full and you're either not backing them
up, or you're backing them up with something that doesn't know to truncate
them afterwards.

If you don't plan on doing point in time recovery of your databases then you
can change your database recovery mode to "Simple" and your transaction logs
won't get so large.  You'll still need to truncate them a time or two after
you switch to Simple as stuff gets committed.

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.

tk

14-Jun-07 07:54 AM

Hello Todd

Thanks for reply.

I am not a sql guy, can you tell me how can I set simple mode to database
recovery mode and how can truncate the existing database log?

I hope by doing this excercise I will not lose any data.

Ashish

14-Jun-07 12:05 PM

You will not lose any data.

Go into the properties of the database in SQL Enterprise Manager or
Management Studio.  Look around for "Recovery Model."  In 2005 it's in the
Options page.  Set the model to Simple.

Wait a few days, then go truncate your Transaction Logs.  That's under Tasks

tk

14-Jun-07 02:37 PM

Hello Todd

Thanks for the help. I checked recover model set as "FULL" thne I changed
to "Simple". Do I need to restart the SQL server again?

I also searched Shrink, did not find it anything.

FYI, I am using sql 2000.

Regards

Ashish

19-Jun-07 05:54 PM

You do not need to restart SQL for a change in the recovery mode to take
effect.


tk

06-Sep-07 02:12 PM

I have been researching to figure out what to do with our Sharepoint SERV database log getting too large.  I currently do have sql backups running for the sharepoint databases, and i know they are set to FULL.  i see in the previous post to change it to Simple, does this affect performance at all?  I am running SQL 2000, not a DBA, how do I truncate the logs, i could not find the option under tasks, i may just be overlooking it.  any help would be greatly appreciated.



tammy

03-Oct-07 10:01 AM

hi Tammy,



After you set your data to "simple" recovery mode, in your enterprise manager databases explorer, right-click on the database you want to shrink the log file, select "All Tasks", then select "Shrink Database..." Click on the "Files..." button (close to bottom right corner), then click on the drop down menu on the top of the next screen, choose your log file.  Select "Shrink file to" (middle of the screen)option then click on the up/down arrow to adjust the number for the desired size of the log file, then click OK.  This should shrink your log to the desired size.  Note:  You might have to run a database backup of that database prior to log shrinking, if you've already have a backup mechanism in place, you won't need to manually back it up.



Good luck,

Dave