The transaction log for database ‘SharePoint_Config’ is full due to LOG_BACKUP

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:

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

The transaction log for database is full due to LOG_BACKUP 1

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. Take a Full database backup.
  2. Shrink the log file to reduce the physical file size (Only in exceptional circumstances).
  3. Create a LOG_BACKUP.
  4. Create a LOG_BACKUP Maintenance Plan.

noteAs 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, 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.

Keep in mind,

warning-iconThe 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 recommended before goes live you should prepare a LOG_BACKUP Maintenance Plan to backup the log file frequently to avoid the shrink operation on Production.


Steps in details:

Take a Full database backup.

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

noteThe 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
  • Alternatively, you can achieve backup operation via SSMS.

Take a full SharePoint Config database backup

Take a full SharePoint Config database backup


Shrink Log file to reduce the physical size.

As I have above mentioned, it’s not recommended to shrink the SharePoint Config database log file, 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.

Increase the Log file size

  • 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

noteIf 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

hint-icon

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.

Index Physical Statistic Report in SQL Server

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

SharePoint Config database - Index Physical Statistic Report in SQL Server

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

SharePoint Config database - Index Physical Statistic Report result in SQL Server

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

Recognize Index in SQL Server

  • Expand Indexes.

Recognize Index in SQL Server

  • Right Click on Each Index > Select Recognize.

hint-iconThis process physically reorganizes the leaf nodes of the index.

Recognize Index in SQL Server

  • The following dialog should be shown > Click OK.

Recognize Index in SQL Server

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

hint-iconThis process drops the existing Index and recreates the index.

Rebuild index

  • The following dialog should be shown > Click OK.

Rebuild index 1

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

Recognize all Index in SQL Server

  • Also, you can Rebuild all indexes.

Rebuild all Index in SQL Server

  • Update Statistic.

hint-iconUpdate 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.

Update Statistics In SQL Server

  • Check Update statistic for this column > OK.

Update Statistics In SQL Server

  • To update statistic for all objects in the database run this T-SQL
Using SharePoint_Config
EXEC sp_updatestats;

noteIn Some cases, Even if you rebuild the index, it would still recommend Rebuild!

idea

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

Rebuild Index maintenance plan wizard

noteA 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


Create a Log Backup.

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

Back up Steps.png

hint-iconLOG_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.

SharePoint Config Database Is Full

noteYou might get this errorBACKUP 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.

BACKUP LOG cannot be performed because there is no current database backup SSMS

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.

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

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

Connect to SQL Server

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

  • From Object Explorer > Management > Maintenance Plan Wizard.

Log back up maintenance plan wizard

  • Click Next.

Log back up maintenance plan wizard

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

Log back up maintenance plan wizard - Schedule

  • I recommended building a frequent schedule, based on your requirement. > OK.

Log back up maintenance plan wizard - Schedule Job

  • The Schedule is assigned > Next.

SharePoint Config database Log back up maintenance plan

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

SharePoint Config database Log back up maintenance plan - Tasks

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

SharePoint Config database Log back up maintenance plan - Tasks Order

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

SharePoint Config database Log back up maintenance plan - Select 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.

SharePoint Config database Log back up maintenance plan - Log Path

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

hint-iconE-mail setting in SQL should be configured to can send the email

SharePoint Config database Log back up maintenance plan - Report

  • Click Finish to complete the Maintenance Plan Wizard.

SharePoint Config database Log back up maintenance plan - Summary

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

SharePoint Config database Log back up maintenance plan - Completed

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

SharePoint Config database Log back up maintenance plan List

SharePoint Config database Log back up maintenance plan - Edit

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

SharePoint Config database Log back up

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

Run SharePoint Config database Log back up

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

Run SharePoint Config database Log back up Path

Run SharePoint Config database Log back up files

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

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

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

  • 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

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.

Restore Multiple transaction log backups in SQL Server

Advertisements

6 thoughts on “The transaction log for database ‘SharePoint_Config’ is full due to LOG_BACKUP

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s