Very Large SharePoint Database Files

Michael Van Cleave Traveling the technical world, learning the language

<< Trike For The Troops 2008 | Home | 50064 Advanced SharePoint Developer Course >>

So you have installed SharePoint (WSS or MOSS) and you have been happily using it for a while now. Everyone in the office has been taking part in creating some really cool content or loading their sites up with some very important information.

Now fast forward a few months. Your local administrator of the server gets in touch with you to let you know that your SQL Server is running low on memory. What? How could this be? You start to look for the source of the issue and low and behold you run across a 120 GB database log file. 120 GB???? Yep. 120 GB.

You start to think to yourself, “Man, I didn’t know that the SharePoint databases were going to take up this amount of memory. Maybe I need to purchase an extremely large drive to put the database files on”.

Well you could do that. Or you can modify a few settings and set up a few good maintenance plans and you can reduce that log file down to mere kilobytes. This actually happened to a few clients of mine. The main source of the problem is that when most folks install SharePoint they don’t realize that it creates the databases and sets them to Full Recovery Mode. What this means is the database log (.ldf) file never gets shrunk. Mainly this is so that if something happens to the database they you have a full history of all of the transactions that have taken place. But, this is bad for many reasons as well. Now don’t get me wrong many places have that grumpy DBA that will take care of all of this stuff for you, but I mainly see this in small to medium organizations that don’t have a full time DBA on staff and/or they roll a development farm to production without a lot of forethought on recovery if something goes bad.

Let’s see what we can do about this.

First open up your file explorer on your database server or whatever server your database files are located on and look at the size of the database .mdf and .ldf files. If you have a default install of SQL Server you can find these files under C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data. Usually if you find that the .ldf files are in the high MB or GB you definitely need to continue with this article. If not at least check to verify that you have database backups and/or maintenance plans in place.

STEP 1: Backup all the SharePoint Databases
If you are going to do anything with the databases it is always a great idea to do a Full backup on each database

STEP 2: Change the Recovery Mode of the database
This is a fairly easy thing to do. You log in to SQL Server and navigate your way to each one of the databases you want to take care of. The following screen shot is of the database properties dialog. Just right click the database and select “Properties”.

The red outlined section is the recovery model setting. Currently it is set to FULL. The other selection “Simple” is the selection we need to use. So select “Simple” and click OK.

STEP 3: Shrink that excessive log file!!!
So the database that I am currently using as an example the log file is nearly 2 GB (actually it is 1.795GB). To shrink the database log file, right click the database and select “Tasks”, then select “Shrink”, then select “Files”. You will get this dialog:

The database file type is what we are after. We want to switch it to “Log”.

Notice the red highlight. Look at the log file size and the available free space. You have the ability to recapture nearly 89% of the space that is currently allocated. Make sure that the shrink action is set to release unused space and click OK. Now your log file should shrink to only the needed space and it should end up around a few hundred KB.

The important take away here is that when you set up SharePoint you need to be sure that you are actively maintaining your system or be sure that you have the proper processes in place for the database. I have seen many of my clients that all this comes as a shock to them. I don’t think it is really their fault, but more just a point that they never knew had to be considered. Most clients that have DBA’s on staff will rarely run in to this problem since most DBA’s will be the first to be sure that these database are fully maintained and recoverable.

On a side note, please be sure that you or someone that is qualified in your organization creates maintenance plans for the databases. This will assist you in being sure that the database is backed up and maintained properly. There are many articles out there on how to set these plans up on SQL Server 2005 so I won’t bore you with that. J

HTH.

Michael

Posted on Tuesday, June 3, 2008 3:06 PM SharePoint | Back to top

Comments on this post: Very Large SharePoint Database Files

# re: Very Large SharePoint Database Files

Also, keep in mind if you are doing transaction log backups, log shipping or mirroring, you need to have the database in full recovery mode.

Microsoft has a list of tested operations done with maintenance plans that they have verified as safe for use with SharePoint
http://support.microsoft.com/kb/932744 and http://support.microsoft.com/kb/841057

Left by Dave Wollerman on Jun 04, 2008 8:02 AM

# re: Very Large SharePoint Database Files

Excellent point.

Thanks for the links.

Michael

Left by Michael on Jun 04, 2008 8:56 AM

# re: Very Large SharePoint Database Files

Hi Michael,

I done this process in my test server which is having 40 GB of log file. When I try to shrink the log file then it is reducing 2% only.
I tried in my production system it works good.
Is it depends on time factor ?
One request from my side(If possible) please post a blog on 'how to change our wss 3.0 from basic to form' (upgrading from WID to SQL Server 2005 Standard).

Thanks.

Left by P.Venkateswara Rao on Jun 07, 2008 5:32 AM

# re: Very Large SharePoint Database Files

Hi Michael,

Exelent post. There is another issue with this logfile. If you have your databases in full recovery mode (maybe other modes too, I didn't try) and set the database (+log) size to a size which is a fair amount under the total disk space available (so it will never eat up all that's left), you could run into the problem that deleting a site collection won't work.
Deleting a site collection seems to be transactional -> the logfile can't hold that information -> the transaction can't be completed, the site collection can't be deleted. The system freezes (seems to freeze) up on such an action, and you better pray it gets back.
If you're really without luck, it thinks it deleted the site collection, but it didn't, preventing you from accessing it and forcing you to delete the database (webapplication).. and start all over.
I have had the pleasure of dealing with both scenarios....
If you setup db and file sizes in such a way that the total disk space will never be taken completely, you will have a better chance that Sharepoint recovers itself and so only preventing you to do very large operations (like delete site collection).
If all disk size is eaten, you're in real trouble, f.i. preventing you to login on the server (especially through remote desktop) and the whole system state is kind of unpredictable. It can mean your site collection is gone but it still sits in your database

Left by Mark Lammerse on Jul 14, 2008 8:03 AM

# re: Very Large SharePoint Database Files

Hi

I have instaled sharepoint on my local machine and was working fine till I discovered that I have low disk space now I want to take all the stuff to centeral server how to carry out the steps

Thanks

Left by Himanshu on Oct 07, 2008 6:26 AM

# re: Very Large SharePoint Database Files

Himanshu,
Well, you have a couple of options that are available to you. You can backup the content that is in your local installaiton and then restore it to a fresh installation on another machine that has more space. Or you can just backup the databases and move them like a migration. Either one should work for you.

Michael

Left by Michael on Oct 07, 2008 8:44 AM

# re: Very Large SharePoint Database Files

Hi, if you cant connect to your sql express database, try this:

1) Start SQL 2005 Express Management Studio
Ã™NOTE : SQL 2005 Express Management Studio must be installed locally on the
Sharepoint host.
2) In the Connect to Server window, enter the following in the Server Name
field
\\.\pipe\mssql$microsoft##ssee\sql\query
3) In the Autentication field, select Windows Autentication
4) Click Connect

Left by Khabba on Feb 04, 2009 5:32 AM

# re: Very Large SharePoint Database Files

or you could read the best practices docs for sharepoint and realize that flipping to simple recovery mode isn't the answer.

increasing the frequency of the log dumps.

Left by Grumpy DBA on Apr 22, 2009 8:25 AM

# re: Very Large SharePoint Database Files

We just followed the above steps and reduced a 60GB SQL DB down to <10GB. We could not believe that our content was breaking 60GB of data. We did a backup of the DB first, and then ran the steps above and then set up a maintenance plan.

Thanks for the info!

Left by Bryan Luce on May 13, 2009 9:34 AM

# re: Very Large SharePoint Database Files

Glad to hear that it helped.

Thanks for visiting my blog.

Michael

Left by Michael on May 13, 2009 10:21 AM

# re: Very Large SharePoint Database Files

Nice, went from 7.3GB to 9MB -- great tip!

Left by Jason Lochan on Jul 06, 2009 1:17 PM

# re: Very Large SharePoint Database Files

great post my man, you saved my day.

The least I can do is THANK YOU.

chears

Left by bashkim gllogovci on Jul 20, 2009 10:16 AM

# re: Very Large SharePoint Database Files

Glad it was helpful.

Michael

Left by Michael on Jul 20, 2009 10:51 AM

# re: Very Large SharePoint Database Files

@Grumpy DBA:

You're right it didn't used to be, but have a look at your SQL logs when you call a backup log with truncate: it's deprecated in favor of simple recovery mode.

Think about it, if you CAN truncate your logs, ie, you don't need point in time recovery, then you can afford simple recovery mode. It feels like you're losing control (I know the feeling) but really it's just deciding up front that you know you won't need point in time restoration.

If you have a cluster or are log shipping or whatever then this probably won't work. It's ok to be grumpy, just don't get stodgy :D

Left by dave on Aug 07, 2009 11:04 AM

# re: Very Large SharePoint Database Files

Brilliant post Michael. It really saved my bacon.

Thanks also to khabba for the "Connect to Server" pipe string.

Left by chuckyman on Dec 08, 2009 2:59 AM

# re: Very Large SharePoint Database Files

I am still facing this issue; my logfile is 700GB. I've tried to truncate but its not working. I will explore changing the recovery mode to simple.....I'm praying it works.

Left by Idris on Feb 17, 2010 9:26 AM

# re: Very Large SharePoint Database Files

You should never, under any circumstances, shrink a database. This causes increadible amounts of fragmentation in the indexes and kills performance.

Unless you don't care about point-in-time recovery, do NOT set a database to SIMPLE mode - very dangerous.

Left by Buck Woody on Mar 29, 2010 4:32 PM

# re: Very Large SharePoint Database Files

Great post man! My db log file was over 300gb and following the steps above got it down to just over 22mg. Unbelievable! I've this problem for a while and was forced to find a solution and I was very happy to find yours. Now I have a maint. plan setup but in reference to Buck Woody, part of my maint plan includeds fragmenting my SharePoint db weekly using this proc I got from this Microsoft article.
http://support.microsoft.com/kb/943345
Maybe you should give it a try. Oh, a comeback to Grumpy......Thanks!

Left by Jose M. Tamez on Apr 08, 2010 10:45 PM

# re: Very Large SharePoint Database Files

Thanks Jose.

Great post on the KB article. It is very true that the databases will become fragemented no matter what you do so this is a great addition to the maint plan.

Thanks for the support.

Michael

Left by Michael on Apr 09, 2010 9:12 AM

# re: Very Large SharePoint Database Files

If someone would be intersted, you can see possible improvement results on typical actions in SharePoint after using proposals from Microsoft WSS database maintenance.

http://www.starsoft.com.pl/en/files/Windows%20SharePoint%20Services%20Content%20Databse%20performance%20results.pdf

Left by Michal Bojko on Apr 12, 2010 1:24 AM

# re: Very Large SharePoint Database Files

Brilliant.
Just recovered 7.5GB space.
Ta very much.

Left by Andy on Sep 15, 2010 9:58 AM

# large size of .log file

i have ms sql server 2008. i tried above procedure suggested by michael but it shrink only the database file (.ldf) but content database log file size (.log) is continuosly increasing and eats up almost 160 GB space of server.
Pls. suggest solution for this also.
Thanks in advance

Left by Om Mahajan on Oct 27, 2010 1:41 AM

# re: Very Large SharePoint Database Files

How can this be done with MSSql express? I have inherited a system that has Sharepoint, but I cannot find whatever application you would launch to change the recovery model.

Left by D. Backer on Nov 17, 2010 5:28 AM

# re: Very Large SharePoint Database Files

Excellent Post!!! Saved my Time alot!!! Thanks!!!

Left by Uma on Dec 23, 2010 2:25 AM

# re: Very Large SharePoint Database Files

What sort of name is "Buck Woody"?
Setting your database to Simple recovery mode is NOT dangerous if you know what you're doing.

Left by Seriously? on Mar 24, 2011 10:39 AM

# re: Very Large SharePoint Database Files

Thank you for sharing to us.there are many person searching about that now they will find enough resources by your post.I would like to join your blog anyway so please continue sharing with us

Left by Generic Propecia on Apr 25, 2011 2:44 AM

# re: Very Large SharePoint Database Files

I wanted to thank you for this great read!! I definitely enjoying every little bit of it I have you bookmarked to check out new stuff you post. SEO Company India

Left by pbs on May 13, 2011 6:26 AM

# re: Very Large SharePoint Database Files

waaahhh...really helpful....gracias! tq very much for ur article....no more headache on log size.....yehhhhhhhhhhh

Left by najwa on Jul 21, 2011 12:14 AM

# re: Very Large SharePoint Database Files

I am getting stuck at step 2. you say 'log in to your sql database', but I cannot do this. When I open SQL server server management studio, it does not list SharePoint as a database, only sbsmonitoring and 2 other databases of our own. How do I open the Sharepoint database?

Left by Alan on Aug 03, 2011 7:05 AM

# re: Very Large SharePoint Database Files

Alan,
It sounds like you might have installed SharePoint with WID or might be pointed at a different SQL Server. If you go in to Central Administration you should be able to identify which SQL Server is being pointed to. If you have SQL Express or WID the connection string will look very different from a standard SQL Server name.

HTH,

Michael

Left by Michael on Aug 03, 2011 11:29 AM

# re: Very Large SharePoint Database Files

Hello!
Here is a way to shrink sharepoint database transaction log for MS SQL Server 2008.
Thanks!

Left by dotNetFollower on Aug 05, 2011 7:59 PM

# re: Very Large SharePoint Database Files

hi

Thanks for your article. it help me a lot. really appreciate your work. GREAT!!!

regards,
wooem

Left by wooem on Sep 19, 2011 3:27 AM

Comments have been closed on this topic.