Shrink SharePoint config database transaction log files

This SharePoint tutorial we will discuss how we can shrink transaction log files in SharePoint 2013/2016. If transaction log files DB is full you will receive an error while doing few operations in SharePoint 2013 or SharePoint 2016.

The error comes are You are running out of disk space on recovery. To free space on this drive by deleting old or unnecessary files, click here…

We all know that SharePoint will have default DB call configDB and in SQL every DB will have a log file to log all the operations/transactions.

As the number of users and transactions are growing high log file size file will grow much faster than transactional DB.

That is what exactly happened in my case the primary DB is only 120GB but whereas log file is 370GB and now server admin team has started sending emails asking me to delete/move this log file.

You are running out of disk space on recovery

You are running out of disk space on recovery. To free space on this drive by deleting old or unnecessary files, click here…

Below is how the error looks like:

Shrink SharePoint config database transaction log files

In SharePoint we can fix this issue by truncating the configDB log file by following the below steps:

This can be done in 2 ways:

• Shrink log file from SQL Server Management Studio
• SQL commands

SQL Server Management Tool (SSMS):

Follow the below steps in SSMS, open SSMS and connect to SharePoint PROD DB server.
Check the below default properties of sharepoint_config DB & sharepoint_log files.

1. Right-click the database, and click Properties, which opens the Database Properties dialog box.
a. Right click on SharePoint_Config
b. Select Properties

2. In the Select a page pane, click Options.

3. View the current recovery model in the Recovery model list box, which should be set to Full
4. Click the dropdown arrow in the recovery model section and select the Simple recovery model
5. Click OK.
6. Right-click on the same database name and click Task-> Shrink-> Files

7. Use the File type drop-down menu and choose Log

8. Follow as advised in screen shot and Click on “OK”

Note: the shrink may take some time depending on how large the file is and how much it has to shrink.

10. After the shrink completes, change the recovery model back to Full by clicking the recovery model dropdown arrow and selecting the Full recovery model

If you try to perform the change with a nonadmin account in SQL Management studio, you will get the below errors.

SQL commands to Shrink log file in Database:

Open SSMS as SQL admin account and perform the below SQL commands.

USE [master]
GO
ALTER DATABASE[SharePoint_Config] SET RECOVERY SIMPLE WITH NO_WAIT
GO
USE [SharePoint_Config]
GO
DBCC SHRINKFILE (‘SharePoint_Config_Log’)
GO
ALTER DATABASE[SharePoint_Config] SET RECOVERY FULL WITH NO_WAIT
GO

Shrink transaction log files in SharePoint 2013/2016

To shrink transaction log files we need to run below SQL commands.

Open your SQL Server management studio then run the below statements:

ALTER DATABASE UrDBName SET RECOVERY SIMPLE

Then Run Below statement:

DBCC SHRINKFILE (UrDBName_Log, 5);

Then run

ALTER DATABASE UrDBName SET RECOVERY FULL;

This will shrink the log file and disk space issue will be recovered.

You may like following SharePoint content database tutorials:

• Backup and Restore content database SharePoint 2013
• Get Content DataBase Size Using PowerShell in SharePoint 2013
• SharePoint content database tutorial

Hope this SharePoint tutorial helps to know how to shrink transaction log files in SharePoint 2013/2016.