Shrink SharePoint Config Log File

In this post, we will show how to Shrink SharePoint Config Log File as a quick solution to minimize the physical size of the transaction log file.

---

Fast Growing SharePoint Config Database Log File

About 2 months ago, the SQL Server drivers had enough space. Suddenly, I noticed that its capacity became full!! I tried to check what’s the reason for the fast growth for driver space, where no new files added to the server lately.

I thought the reason may be from Logs files that grow based on the amount of transaction on the server. I went back to the SQL Server database path, then make sort with size desc.

I noticed SharePoint_Config_log.ldf  file size is about 14 GB.

As a best practice, you should check the possible responses to a full transaction log and suggested how to avoid it in the future. ant think about Transaction log backup. but if you need to just free the physical log file size you should Shrink SharePoint Config Log File as mentioned below:

Reduce SharePoint Config Log File Size

Required Permission

To Shrink SharePoint Config Log File, you will need an account has membership in the sysadmin fixed server role or the db_owner fixed database role.

Perform a full database backup

If you are going to do anything with the databases it is always a great idea to do a Full backup on each.

Change Database Recovery Mode to SIMPLE

• Navigate your way to each one of the databases you want to take care of. Just right-click the database and select “Properties”.

• In Compatibility Level Change it from Full to Simple.

Shrink SharePoint Config Log File

• Right-click the database and select “Tasks”–> Select “Shrink”.
• Select log from File Type –> Set to release unused space and click OK.
• Selecting release unused space causes any unused space in the file to be released to the operating system and shrinks the file to the last allocated extent. This reduces the file size without moving any data.
  • Optionally, select the Reorganize Pages before releasing unused space checkbox. If this is selected, the Shrink file to value must be specified. By default, the option is cleared.
  • Optionally, select the Empty file by migrating the data to other files in the same filegroup checkbox. this option moves all data from the specified file to other files in the filegroup.

•  Now the log file should shrink to only the needed space and it should end up around a few hundred KB.
• Change the Recovery mode again to Full.

Shrink SharePoint Config Log File using T-SQL

use sharepoint_config
go
alter database sharepoint_config set recovery simple
go
dbcc shrinkfile('SharePoint_Config_log',100)
go
alter database sharepoint_config set recovery FUll
go

In some cases, you can’t achieve Shrink operation and you got the below error. so in this case, you should check The transaction log for database SharePoint_Config is full due to LOG_BACKUP

Note: The shrink operation will affect the SQL Server Performance. therefore, It’s strongly recommended that before go live you should prepare a LOG_BACKUP Maintenance Plan to backup the log file frequently and truncate the SharePoint Config Log File so you will avoid the fast-growing for the transaction log files!

---

Conclusion

Actually, it’s not recommended to perform a shrink operation to the transaction log file, instead, you should create a log backup to Minimize work loss risks and truncate the transaction log file size and prevent it from filling. But as a quick solution to free the physical disk space, you can perform a shrink operation.

Applies To

• SQL Server.
• SharePoint Server.
• SharePoint Config Database.

You might also like to read

• Shrink a Transaction Log File Maintenance Plan in SQL Server 2016.
• Create Transaction LOG BACKUP Maintenance Plan in SQL Server
• Responding to a full transaction log
• The possible responses to a full transaction log and suggested how to avoid it in the future.