Truncate a SharePoint database log file

Since SharePoint is heavily dependent on SQL Server to store not only content but configuration information about the environment, there is a lot of emphasis placed on the design, configuration, scalability and health of SQL Server.

One area that we see a lot of questions on is:

  1. What should the default recovery model be for SharePoint databases?
  2. How can I truncate the log file to recovery disk space.

When it comes to the default recovery model for SharePoint databases the answer is depends (I know)!  Because SharePoint uses quite a few databases to scale all of the content and service applications, each one has it's own recovery model recommendations.  Be sure these recommendations line up with your backup plan to prevent any unwanted data loss.

In case you have a runaway log file that needs to be truncated, here is some TSQL that can be executed on the SharePoint SQL Server.

USE [database]

-- Set to SIMPLE mode


-- Shrink the database log file

--This name of the log file should be the same name as what is on the disk.  If your not sure run this command to find out.

--SELECT name, physical_name AS current_file_location FROM sys.master_files

DBCC SHRINKFILE ('database_log', 1);

-- Set back to FULL (optional depending on backup method used)



Comments (1)
  1. Ekrem Önsoy says:

    This should be noted that changing the recovery model to Simple will break the transaction log backup chain. So for instance if there’s a Log Shipping configuration in the environment, it will be broken.

Comments are closed.

Skip to main content