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 transaction log for SharePoint_Config Database.

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

Once The transaction log for database ‘SharePoint_Config’ is full due to ‘LOG_BACKUP’ error is encountered. no transaction or action can be performed on ‘SharePoint_Config’ database.

In this article, I’ll show How to avoid falling into this issue by doing 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.

noteIn case you have 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 action on ‘SharePoint_Config’ database as I above mentioned.

So you will not be able to take a backup, and in this case, you should start from step 2 (Shrink the log file to reduce the physical file size) then go back to get a full dattabase backup as prerequisite for perofmaing LOG_BACKUP.

Keep in mind,

warning-iconThe Shrink operation effects on SQL Server Performance during executing shrink command. it also causes index fragmentation and can slow the performance of queries that search a range of the index.

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:

(1) 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.

Back up Steps.png

full back up database.png

(2) Shrink Log file to reduce the physical size.

Only in exceptional circumstances, you might need to shrink the physical log file because you couldn’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 space is available 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.

Autogrow log file

  • Turn on auto growth by using the ALTER DATABASE statement to set a non-zero growth increment for the FILEGROWTH option.

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. By running the following below commands.


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.

Open Index Physical Statstic Report.png

  • As the report name says, it shows how the index is physically laid out on data files. Here is how the report looks like:

Index Physical Statstic report.png

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

Index Physical Statstic report 1

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

Index Physical Statstic report 2

  • Expand Indexes.

Index Physical Statstic report 3

  • Right Click on Each Index > Select Recognize.

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

Recognize index

  • The following dialog should be shown > Click OK.

Recognize index 1.png

  • 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

  • Also, you can Rebuild all indexes.

Rebuild all index.png

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

  • Check Update statistic for this column > OK.

Update Statstic 1

  • 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 and recognizing index automatically.

maintinance plan wizard 3

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

(3) 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. 

To take a log backup using T-SQL


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

GO

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

BackUp log from SSMS

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

(4) 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 SSMS

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.

maintinance plan wizard

  • Click Next.

maintinance plan wizard 1

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

maintinance plan wizard 2

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

New Job Schedule

  • The Schedule is assigned > Next.

maintinance plan wizard 3

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

maintinance plan wizard 4

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

maintinance plan wizard 5

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

maintinance plan wizard 6

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

maintinance plan wizard 7

  • 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

maintinance plan wizard 8

  • Click Finish to complete the Maintenance Plan Wizard.

maintinance plan wizard 9

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

maintinance plan wizard 10

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

maintinance plan wizard 16

maintinance plan wizard 11

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

maintinance plan wizard 12.png

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

maintinance plan wizard 13

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

maintinance plan wizard 14.png

maintinance plan wizard 15

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 back up

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s