Tuesday, June 03, 2008 3:06 PM
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