One of the annoying issues that you may experience when you work with SharePoint Databases is

The rapid growth of the SharePoint Config Database transaction log (Full).

So you should regularly maintain the SharePoint_Config Database by taking a backup of the log files frequently to avoid the below-encountered error:

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

Note: When The transaction log for database ‘SharePoint_Config’ is full due to ‘LOG_BACKUP’ error is encountered. No transaction or backup action can be performed on ‘SharePoint_Config’ database.

---

In this article, I’ll show How to avoid falling into this issue (SharePoint_Config Database log file is Full) by following the below steps:

Steps:

1. Shrink the log file to reduce the physical file size.
2. Take a Full database backup.
3. Create an LOG_BACKUP.
4. Create an LOG_BACKUP Maintenance Plan.

Steps in details:

 

Shrink Log file to reduce the physical size.

Keep in mind,

The Shrink operation effects on the SQL Server Performance during executing shrink command. besides It causes index fragmentation that leads to slowness and performance issue.

Therefore, It’s not recommended to shrink the SharePoint Config database log file, it’s recommended before goes live you should prepare an LOG_BACKUP Maintenance Plan to backup the log file frequently to avoid the shrink operation on Production.

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

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

For more details check Troubleshoot a Full Transaction Log

If you are unable to perform any of the previous alternative solutions. In this case, you can perform a shrink operation to reduce the physical file size. 

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.

After shrink operation has been completed, you should consider rebuilding the indexes on the file to eliminate the fragmentation by doing the following:

• 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. ONLINE option will keep index available during the rebuilding.

• 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.

• 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!

Honestly, the above steps are a headache, so I suggest to create a maintenance plan to perform rebuilding or recognizing index automatically.

A maintenance plan should include either index reorganization or index rebuilding; not both.

For more details Check How to prepare a Shrink a log file Maintenance Plan

---

Take a Full database backup.

It’s highly recommended to perform a full database backup before going to perform any action on it. Meanwhile, it’s a prerequisite for performing an LOG_BACKUP.

The account that performs a BACKUP DATABASE and BACKUP LOG must have a permission of the sysadmin fixed server role and the db_owner fixed database roles.

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

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

As I have mentioned, if you already got this error “The transaction log for database ‘SharePoint_Config’ is full due to ‘LOG_BACKUP’” you will not be able to perform any transaction or backup action on ‘SharePoint_Config’ database.

In this case, you will not be able to take a backup using management studio, and you should start from step 2 (Shrink the log file to reduce the physical file size) then go back to get a full database backup as a prerequisite for performing LOG_BACKUP.

• Alternatively, you can achieve backup operation via SSMS.

---

Create a Log Backup.

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. 

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.

You might get this error : BACKUP LOG cannot be performed because there is no current database backup.

Therefore, you must have a full database backup before you take a log backup. As I mentioned in the first step.

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

BACKUP DATABASE [SharePoint_Config] TO DISK = N'C:\SharePoint_Config.bak'

GO

BACKUP LOG [SharePoint_Config] TO DISK = N'C:\SharePoint_Config_log.bak'

GO

---

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.

Log truncation does not reduce the size of the physical log file.

To reduce the size of a physical log file, you need to shrink the log file that can occur only

• While the database is online.
• At least one virtual log file is free.

• Login to SQL Server.

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

• From Object Explorer > Management > Maintenance Plan Wizard.

• Click Next.

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

• I recommended building 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.

In case of the database was in recovery, try to recover the database by urunning the below command:

ALTER DATABASE database_name SET ONLINE.

In the previous section, I discussed the possible responses to a full transaction log and suggested how to avoid it in the future.

In the following section, I will discuss How to Restore a transaction log backup.

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

Example: 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

Example: Restoring Multiple transaction log backups using SSMS.