A database backup completes with exception: V-79-40960-37914 - Database DATABASENAME is configured to maintain transaction logs. Transaction log backups are not being performed
Problem
A database (Microsoft SQL, Microsoft SharePoint & Enterprise Vault) backup completes with the follow exception in the job log:
Error Message
V-79-40960-37914 - Database DATABASENAME is configured to maintain transaction logs. Transaction log backups are not being performed. This will result in the log growing to fill all available disk space. Regular log backups should be scheduled or the database should be changed to the simple recovery mode.
Cause
- Agent for Microsoft SQL Servers
- Agent for Microsoft SharePoint Servers
- Agent for Enterprise Vault
This exception is generated after a backup has been performed of SQL databases that are set with a Recovery Model of "Full" or "Bulk-logged". However, if it is set to "Simple" , it will not generate an exception. This setting is configured by accessing the database properties >>> Option on SQL Server.
When it is set to "Full", the transaction logs will be managed by Backup Application. When it is set to "Simple", transaction logs will be managed by SQL itself. The drawback with setting it to "Simple" is it will not allow point in time recovery for the database. Therefore, the recommended setting is "Full" or " Bulked".
This exception will not occur on every backup of a SQL database. The behavior on each version of Backup Exec is as follows.
BackupExec 21 or later:
This exception is generated when the 33rd Full backup of SQL Databases completes if the databases have the considerable size (over 5000 pages). The exception will be displayed again when the databases increase continuously until the transaction logs are backed up.
The exception is designed to help customers manage the size of SQL databases and transaction logs. Veritas recommends scheduling regular log backups of databases. This will help keep the transaction logs at a manageable size.
For more information about SQL Recovery Modes, please visit the following Microsoft TechNet article below:
http://technet.microsoft.com/en-us/library/ms189272.aspx
Solution
For Microsoft SQL:
Create a backup with a Backup Method of "Log - Back up Transaction Log". This can be set in the Microsoft SQL section of the backup job properties.
For Microsoft SharePoint:
Create a backup with a Backup Method of "Log - Back up and truncate transaction Log." This can be set in the Microsoft SharePoint section of the backup job properties.
For Enterprise Vault:
Create a backup with a Backup Method of "Incremental." This can be set in the Enterprise Vault section of the backup job properties.
To configure this exception to show after a larger number of backups than the default number or not to show the exception the below registry key on the Microsoft SQL Server will need to be adjusted.
Warning: The resolution of this issue requires the registry to be edited with the Windows registry editor. Incorrect use of the Windows registry editor can have disastrous results on a server operating system. Great care should be taken when making changes to a Windows registry. Registry modifications should only be carried-out by persons experienced in the use of the registry editor application. It is recommended that a complete backup of the registry and workstation be made prior to making any registry changes.
HKEY_LOCAL_MACHINE\SOFTWARE\Veritas\Backup Exec for Windows\Backup Exec\Engine\SQL Server\Number of Non-Log Backups Before Warning
The default value is as follows.
In the case of Backup Exec 21 or later: A decimal value of 32
Increasing this value will increase the number of full database backups it will take to display the exception.