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. 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
• How to get content database size using PowerShell in SharePoint 2016 and using the SharePoint server object model
• How to know which content database a site collection is using in SharePoint 2016 or SharePoint 2013?
• Retrieve all site collections under particular content databases using PowerShell and SharePoint 2016 server object model
• How to retrieve all content database names for SharePoint 2016 web applications using the SharePoint server object model?
• Get content database sizes for all web applications in a SharePoint farm using PowerShell
• Prevent new sites to be added to content databases in SharePoint 2013
• Move Site Collections to other content databases in SharePoint 2013

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