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:
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
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);
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.
I am Krishna.Vandanapu a SharePoint architect working in IT from last 13+ years, I worked in SharePoint 2007, 2010, 2013, 2016 and Office 365. I have extensive hands on experience in customizing SharePoint sites from end to end. Expertise in SharePoint migration tools like Sharegate, Doc Ave and Metalogix. Migrated SharePoint sites from SharePoint 2007 to 2010 and 2010 to 2013 several times seamlessly. Implementing CSOM with Microsoft best practices. Spent quality time in configuring SharePoint application services like User Profile, Search, Managed Meta data services etc. Now exploring SharePoint Framework and SharePoint 2019