The transaction log for database SharePoint_Config is full due to LOG_BACKUP

One of the wired issues that you may experience when you work with SharePoint Databases is the rapid growth of the SharePoint Config Database transaction log file and encountering”The transaction log for database ‘SharePoint_Config’ is full due to LOG_BACKUP” error.

You might also like to read SQL Server Best Practices for SharePoint 2019.

In this article, we’ll explain How to avoid falling into this issue (SharePoint_Config Database log file is Full) and solving “The transaction log for database ‘SharePoint_Config’ is full due to LOG_BACKUP” error by exploring the following:

---

The transaction log for database SharePoint_Config is full due to LOG_BACKUP

If the SharePoint_Config database transaction log becomes full, the SQL Server Database Engine will raise a 9002 error. 

The transaction log for database ‘SharePoint_Config’ is full due to ‘LOG_BACKUP’ (Microsoft SQL Server, Error : 9002)

So you should regularly maintain the SharePoint_Config Database by taking a backup of the log files frequently to avoid this error.

Note: When The transaction log for database ‘SharePoint_Config’ is full due to ‘LOG_BACKUP’ error is encountered. the database can only be read, but not updated! No transaction or backup action can be performed on ‘SharePoint_Config’ database.

---

Troubleshoot a Full Transaction Log

Before we getting started, you should be aware of the Shrink operation affects the SQL Server Performance during executing shrink command, and It causes index fragmentation that leads to slowness and performance issues.

Therefore, It’s not recommended to shrink the SharePoint Config database log file! instead, before goes live, it’s strongly recommended to prepare a LOG_BACKUP Maintenance Plan to backup the log file frequently to avoid the shrink operation on the production environment.

However, in some exceptional circumstances, you may need to shrink the log file to reduce the physical disk space if you can’t perform one of the following shrink alternative solutions:

• Freeing disk space so that the log can automatically grow.
• Moving the log file to a disk drive with sufficient space.
• Increasing the size of a log file.
• Adding a log file on a different disk.

Freeing disk space so that the log can automatically grow

You might be able to free disk space on the disk drive that contains the transaction log file for the database by deleting or moving other files. The freed disk space allows the recovery system to enlarge the log file automatically.

Moving the log file to a disk drive with sufficient space

If you cannot free enough disk space on the drive that currently contains the log file, consider moving the file to another drive with sufficient space. Note: Log files should never be placed on compressed file systems.

To move a log file check Move Database Files.

Increasing the size of a log file

If there is available space on the log disk, you can increase the size of the log file. The maximum size for log files is two terabytes (TB) per log file.

To increase the file size If the auto growth is disabled, the database is online and sufficient space is available on the disk, either:

• Manually increase the file size to produce a single growth increment.

• Turn on auto growth using T-SQL.

ALTER DATABASE EmployeeDB
MODIFY FILE
( NAME = SharePoint_Config_log,
SIZE = 2MB,
MAXSIZE = 200MB,
FILEGROWTH = 10MB );

Adding a log file on a different disk

Add a new log file to the database on a different disk that has sufficient space by using ALTER DATABASE ADD LOG FILE. To add a log file Add Data or Log Files to a Database.

---

SharePoint Config Database transaction log is full

In the previous section, we have explained the effect of shrink operation and how to troubleshoot the Full Transaction Log without performing a shrink.

In this section, we’ll introduce four steps to solve “The transaction log for database SharePoint_Config is full due to LOG_BACKUP” If you are unable to perform any of the previous alternative solutions.

1. Shrink Log file to reduce the physical size.
2. Take a Full database backup.
3. Create a Log Backup.
4. Create a Maintenance Plan to take backup logs frequently.

Step 1: Shrink Log file to reduce the physical size

Again, in some exceptional circumstances, you may need to perform a shrink operation to reduce the physical file size of the SharePoint Config Database Transaction Log File.

Shrink SharePoint Config Database 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

100 is called the target_size for the file in megabytes, expressed as an integer. If not specified, DBCC SHRINKFILE reduces the size to the default file size. The default size is the size specified when the file was created.

Index Physical Statistics

As we earlier mentioned, the shrink operation

• Affects the SQL Server Performance during executing shrink command.
• Causes index fragmentation that leads to slowness and performance issues.

So that after performing a shrink operation, it’s strongly recommended to consider rebuilding the indexes on the file to eliminate the fragmentation by doing the following:

Steps

• Check The indexes fragmentation by opening the Index Physical Statistic Report.

• This report shows how the index is physically laid out on data files.

• Check the Operation Recommended column to check the objects that need to reorganize or rebuild. In this case, it’s a Classes Table.

– Index should be rebuild when index fragmentation is greater than 40%.

– Index should be reorganized when index fragmentation is between 10% to 40%.

– Index rebuilding process uses more CPU and it locks the database resources.

– SQL Server development version and Enterprise version has option ONLINE, which can be turned on when Index is rebuilt. The ONLINE option will keep the index available during the rebuilding.

Recognize Physical Indexes

• Go back to the database that has already shrunk  > Expand Tables > Select the Table that needs to recognize or rebuild. In this case, it’s a Classes Table.

• Expand Indexes.

• Right Click on Each Index > Select Recognize.

This process physically reorganizes the leaf nodes of the index.

• The following dialog should be shown > Click OK.

Rebuild Physical Indexes

• Again with the same previous steps > Select Rebuild for the index that needs to rebuild in your table based on the index physical statistic report.

This process drops the existing Index and recreates the index.

• The following dialog should be shown > Click OK.

• Again, apply the previous steps to all index in the table, but it’s a boring job that will take more time to operate.So, you can recognize all indexes by clicking right on Indexes folder and select Recognize All.

• Also, you can Rebuild all indexes.

Update Statistic.

Update Statistics task

• Ensures the query optimizer has up-to-date information about the distribution of data values in the tables.
• Allows the optimizer to make better judgments about data access strategies.

• Check Update statistic for this column > OK.

• To update statistic for all objects in the database run this T-SQL

Using SharePoint_Config
EXEC sp_updatestats;

In some cases, Even if you rebuild the index, it would still recommend Rebuild!

At the end of this section, if you find the operation of recognizing and rebuilding index is boring task, so we suggest creating a maintenance plan to perform rebuilding or recognizing index automatically as mentioned at Shrink a log file Maintenance Plan.

---

Step 2: Take a Full database backup

Full database backup is a mandatory prerequisite for performing LOG BACKUP to avoid The transaction log for database SharePoint_Config is full due to LOG_BACKUP error.

• Run the following command to achieve a full database backup using T-SQL:

BACKUP DATABASE SharePoint_Config TO DISK = 'C:\SharePoint_Config.BAK'
GO

Note: a BACKUP DATABASE and BACKUP LOG require the account to have a sysadmin fixed server role and the db_owner fixed database roles.

• Alternatively, you can achieve backup operation via SSMS.

---

Step 3: Create a Log Backup

Take a log backup using T-SQL

BACKUP LOG SharePoint_Config TO DISK = 'C:\SharePoint_Config_log.bak'
GO

Take a log backup using SSMS

• Right-click on the database name.
• Select Tasks > Backup.

LOG_BACKUP working with Full or bulk-logged recovery models only.

• Select “Transaction Log” as the backup type.
• Select “Disk” as the destination.
• Click on “Add…” to add a backup file.
• Type "C:\SharePoint_Config.Bak" and click “OK”.
• Click “OK” again to create the backup.

If you didn’t perform full database backup, you will get this error BACKUP LOG cannot be performed because there is no current database backup.

Also, you can apply a bundle command to take a full backup and log backup at once:

-- Perform database backup
BACKUP DATABASE [SharePoint_Config] TO DISK = N'C:\SharePoint_Config.bak'
GO
-- perform log backup
BACKUP LOG [SharePoint_Config] TO DISK = N'C:\SharePoint_Config_log.bak'
GO

Note: If the log file has never been backed up before. you must create two log backups to permit the Database Engine to truncate the log to the point of the last backup. 

---

Step 4: Create a Maintenance Plan to take backup logs frequently

Maintenance Plan allows you to perform various database administration tasks including backups, database integrity checks, or database statistics updates on a regular basis.

You must be a member of the ‘sysadmin’ fixed server role, to can create and manage Maintenance Plans. 

Create Log backup maintenance plan

• Login to SQL Server.

• From Object Explorer > Management > Maintenance Plan Wizard.

• Click Next.

• Specify a meaning name > Click Change to Set a schedule.

• It’s recommended to build a frequent schedule, based on your requirement. > OK.

• The Schedule is assigned > Next.

• In Maintenance Tasks Select Backup Transaction database log > Next.

• In the case of selecting more task, you could be able to specify their order here > Next.

• Define backup database task by selecting all database or a specific database.

• Specify
  • The folder to contain the automatically created database files.
  • The extension backup file. (The default is .trn).
  • Check verify the backup integrity to make sure the backup has been taken and completed properly and all volumes are readable.

• Specify the report path > Check E-mail report if you need to receive a report.

E-mail setting in SQL should be configured to can send the email.

• Click Finish to complete the Maintenance Plan Wizard.

• The maintenance plan should be now created successfully > Close.

• Go back to Management section to make sure that the Maintenance plan has been created properly.

• Also, Go back to SQL Server Agent > Jobs > Make sure that the job has been connected to the Maintenance Plan.

• To test the Maintenance Plan > Right click on Job and Start. > it should be now started and completed successfully.

• Go to the backup location to make sure that the log backup has been created successfully.

You might also like to read Create a LOG_BACKUP Maintenance Plan in SQL Server

---

Restore Log Backup

In the previous section, we discussed the possible responses to a full transaction log and how to avoid “The transaction log for database SharePoint_Config is full due to LOG_BACKUP” error in the future.

In the this section, we will learn How to Restore a transaction log backup.

Execute the RESTORE LOG statement to apply the transaction log backup, requires:

• The name of the database to which the transaction log will be applied.
• The backup device where the transaction log backup will be restored from.
• The NORECOVERY clause.

The basic syntax for this statement is as follows:

RESTORE LOG database_name FROM WITH NORECOVERY

Where database_name is the name of the database and is the name of the device that contains the log backup is restored.

• After restoring the last backup in your restore sequence, to recover the database using the following statement:

RESTORE database_name WITH RECOVERY

Note: The restore of SharePoint_Config database is only allowed on the same farm.

Restoring a single transaction log backups using T-SQL

RESTORE DATABASE SharePoint_Config FROM SharePoint_Config_1 WITH NORECOVERY GO
RESTORE LOG SharePoint_Config FROM SharePoint_Config_log_1 WITH FILE = 1, WITH NORECOVERY
GO
RESTORE DATABASE SharePoint_Config WITH RECOVERY
GO

Restoring Multiple transaction log backups using SSMS.

---

Conclusion

In conclusion, we have learned how to solve “The transaction log for database SharePoint_Config is full due to LOG_BACKUP” error.

Applies To

• SharePoint 2019.
• SharePoint 2016.
• SharePoint 2013.
• SharePoint 2010.

You might also like

• Troubleshoot a Full Transaction Log.
• SQL Server Best Practices for SharePoint 2019.
• Create a LOG_BACKUP Maintenance Plan in SQL Server.
• Shrink a transaction log file Maintenance Plan in SQL Server.

Have a Question?

If you have any related questions, please don’t hesitate to ask it at deBUG.to Community.