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:

(of course, being WSS_Content_DB your database name).

2 thoughts on “Truncate that Huge DB Transaction Log!”

  1. In my opinion, the best option is include the truncate of the logs in the maintenance plans for the content DBs in order to reduce it after each full backup.

    Setting the recovery mode to simple it is not recommended for a critical SharePoint site.

  2. Àlex, thank you for your tip.

    Of course, for mission-critical sites a FULL RECOVERY model is a must, but for a small databases SIMPLE recovery model is adequate enough if your Service Level agreements allows for lost data between backups.

    SIMPLE recovery model will not preserve data that has been added after the last backup.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.