Truncate a SharePoint database log file

  • Article

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 it...it 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

ALTER DATABASE [database] SET RECOVERY SIMPLE;

-- 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)

ALTER DATABASE [database] SET RECOVERY FULL;

References: