Does This Ring a Bell?

SharePoint 2010 was set up months ago, and now the database LDF files are huge. Server hard disk is full or nearly so. Symptoms can be

1. Can no longer upload (sometimes just larger) files to the SharePoint document libraries
2. Other updates in SharePoint do not work properly or at all
3. Hard disk is full

You Can't Get There from Here

It seems like every blog and forum post dive into topics that a true database administrator (DBA) would know or care about. And even when you do understand what they are saying, the solutions provided are not trivial or possible. Fear not. Here is…

A Quick Fix:

If you are not a DBA, here are some steps you can take to get your SharePoint site working again. For each database where the log files are big (say, over 10 GB)

1. Put your database into simple recovery mode
2. Shrink your LDF file(s).

Method 1: SQL GUI Steps via Server Management Studio

• Right-click the database > Properties > Options > Recovery Model
• Change from Full to Simple
• Ok
• Right-click the same database again > Tasks > Shrink > Files
• Change the File Type dropdown from Data to Log
• Ok

Method 2: Scripting

USE <databaseName>

GO

ALTER DATABASE SharePoint_Config SET RECOVERY SIMPLE

DBCC SHRINKFILE(N'<databaseName>', 1)

GO

For example,

USE SharePoint_Config

GO

ALTER DATABASE SharePoint_Config SET RECOVERY SIMPLE

DBCC SHRINKFILE(N'SharePoint_Config_log', 1)

GO

For items with dashes like those with a GUID, surround the database name with []. I use 100 (MB) here for the target file size in case I want to go back to full recovery for this database.

USE [WebAnalyticsServiceApplication_ReportingDB_cd98775b-b19e-4376-b782-c8d7045a79e4]

GO

ALTER DATABASE [WebAnalyticsServiceApplication_ReportingDB_cd98775b-b19e-4376-b782-c8d7045a79e4] SET RECOVERY SIMPLE

DBCC SHRINKFILE(N'WebAnalyticsServiceApplication_ReportingDB_cd98775b-b19e-4376-b782-c8d7045a79e4_log', 100)

GO

The first parameter in SHRINKFILE() is the logical file name, not the physical filename that you see on the file system. Sometimes the logical log file is not <databaseName_log>. You can determine the logical file name of the log via Right-click on database > Properties > Files

After clicking pressing F5 or clicking Execute to run the script, click on the Messages tab to see if there were any issues.

Note that this removes the ability to restore to a point in time which means that you may restore to the last full backup.

What to do long-term

Consider recovery model. There are two options:
1. Simple means you can restore to the last time you made a backup which is what most people think of when they think "backup and restore". The above process puts the database into Simple recovery model.
2. Full allows doing the restore like in the Simple model. But is also allows one to do a a point in time to restore using both the last backup and the log files since the last backup. Follow the links below in the References section to learn more.

References

http://msdn.microsoft.com/en-us/library/ms345382.aspx
http://technet.microsoft.com/en-us/magazine/2009.07.sqlbackup.aspx

http://sqlblog.com/blogs/tibor_karaszi/archive/2007/02/25/leaking-roof-and-file-shrinking.aspx
http://www.karaszi.com/SQLServer/info_dont_shrink.asp