Working with Large SharePoint Database files

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. 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

The below approach applies for Sql Server 2005

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”. Open Sql Server Management Studio à Under your Server Name Node Expand Databases Nodeà Select the Database you want to shrink à right click and select Properties which opens a new popup window with properties à Click options on the left tab and select “Simple” instead of “Full” for Recovery Model as shown below 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.

-------------------------------------------------------------------------------------------------

Alternately you can follow the beow approach:

You might run into a scenario on your SharePoint development box where the WSS_Content_log file grows to be quite large. If you’re limited on hard drive space and need to know how to truncate and shrink this log file, follow the directions below. For my particular installation, I had WSS 3.0 and SSEE installed with all the defaults.
Before we begin, you may want to take a bit of time to learn about Recovery Models, Truncating and Shrinking.
Let’s start. Open Microsoft SQL Server Management Studio Express. If you don’t have that tool installed, you can get it free from Microsoft. Expand Databases. Right click on the WSS_Content database, go to Properties, Options, and change Recovery model: to Simple. Click OK.
Click New Query. Once the blank page opens up, make sure the WSS_Content database is selected.
Run the following command first. This will truncate the log file. We must do this step first before we can shrink the file.

BACKUP LOG WSS_Content WITH TRUNCATE_ONLY
The next step will actually shrink the log file and recover our disk space. You can use the command below to confirm the name of the file we’ll be shrinking.

SELECT * FROM sys.sysfiles
The name column will give us the information for the last command – the name of the log file itself. In this case, it’s WSS_Content_log:

DBCC SHRINKFILE(WSS_Content_log, 1)
That should do it. I had a 4 gig content db and a 26 gig log file. All of the above took about 5 minutes or so.