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:
The transaction log for database ‘SharePoint_Config’ is full due to ‘LOG_BACKUP’ (Microsoft SQL Server, Error : 9002)
Once The transaction log for database ‘SharePoint_Config’ is full due to ‘LOG_BACKUP’ error is encountered. The SQL Server raises a 9002 error, hence no transaction or action can be performed on ‘SharePoint_Config’ database.
In this article, I’ll show How to overcome this issue by doing the following below steps:
- Take a Full database backup.
- Shrink the log file to reduce the physical file size (Only in exceptional circumstances).
- Create a LOG_BACKUP.
- Create a LOG_BACKUP Maintenance Plan.
Keep in mind,
The 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 go 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.
It’s highly recommended to perform a full database backup before going to perform any action on it.Meanwhile, it’s a prerequisites for performing a 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
- Alternatively, you can achieve back up operation via SSMS.
(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.
- Turn on auto growth by using the ALTER DATABASE statement to set a non-zero growth increment for the FILEGROWTH option.
ALTER DATABASE EmployeeDB
( 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. 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
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.
- As the report name says, it shows how the index is physically laid out on data files. Here is how the report looks like:
- Check the Operation Recommended column to check the objects that need to recognize 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 is a headache, so I suggest to create a maintenance plan to perform rebuilding and 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
(3) 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.
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.
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.
"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
(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.
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.
- 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 startd 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.
- 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 being restored.
- After restoring the last backup in your restore sequence, to recover the database use 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 WITH FILE = 1, WITH NORECOVERY GO RESTORE DATABASE SharePoint_Config WITH RECOVERY GO
Example: Restoring Multiple transaction log backups using SSMS.