SBS 2008 C drive runs out of space - shrink SharePoint database log file

I have seen serveral cases recently where a server running Small Business Server 2008 has run out of space on the C Drive. Using Tree Size, I identified a folder which contained a large amount of data:

C:\Windows\SYSMSI

The area of this folder which contained all the data was

C:\Windows\SYSMSI\SSEE\MSSQL.2005\MSSQL\Data

This folder contained many SQL databases and log files, and after some research I discovered that my largest files were Transactional Log files for Sharepoint.

The biggest of these files was


SharePoint_Config_ffffffff-ffff-ffff-ffff-fffffffffffffffffff_log.LDF

 and was up to 16Gb in size, but other culprits were


ShareWebDb_log.LDF
WSS_Content_fffff..._log.LDF




Large SharePoint log files

 




All these files are log files which can easily be shrunk down to a manageable size. SQL Server Management Studio is required for this.


To run SQL Management Studio, select

Start > All Programs > Microsoft SQL Server 2005 > SQL Server Management Studio Express

 

















Server name is
\\.\pipe\mssql$microsoft##ssee\sql\query
 
Then select Connect

Expand Databases and locate the datebase with the large log file

Right click on the database and select New query

























The query window will appear on the right. Enter the following text:

ALTER DATABASE Database_name SET RECOVERY SIMPLE;
GO

Then click the Execute! button


If the database name contains a ' - ', you will get an error message (below).

In this case, the database name must be surrounded by " ", eg:


ALTER DATABASE "SharePoint_Config_29c26fca-17b8-48c1-9704-b869932abcb6" SET RECOVERY SIMPLE;
GO


Then select Execute!





You should see a message which reads Commad(s) completed successfully

Next, Select the database

Right click > Tasks > Shrink > Files

Change File type to Log

Click OK

Once this process has completed, right click the database again and select New query

Enter the following command (use " " around database_name if it contains ' - ')

ALTER DATABASE Database_name SET RECOVERY FULL;

GO

Execute!

Browse to the folder location

C:\Windows\SYSMSI\SSEE\MSSQL.2005\MSSQL\Data

and you should see a big difference in the size of the log file.