Truncate that Huge DB Transaction Log!

How many times you’ve seen the small SharePoint content database and a huge transaction log by its side? And the corresponding “Not enough storage space” warning on the SQL Server?

The cause of this behaviour is that the SharePoint databases are created with FULL RECOVERY transaction log. It essentially creates a full record of every sentence sent to the database, even if it’s only a SELECT. As you can imagine, SharePoint does a lot of querying to the database every second and the transaction log grows very quickly indeed.

To prevent this behaviour, change the RECOVERY mode of the transaction log to SIMPLE. But, if you are coping with a huge transaction log already, you can use this snippet of T-SQL to truncate it back to inoffensive 1 MB in size:

USE WSS_Content_DB;
GO
ALTER DATABASE WSS_Content_DB
SET RECOVERY SIMPLE;
GO
DBCC SHRINKFILE (WSS_Content_DB_Log, 1);
GO

(of course, being WSS_Content_DB your database name).