Veeam Backup warning “Unable to truncate Microsoft SQL Server transaction logs.”
I had a customer that contacted me to day because he got a warning that Veeam Backup was "Unable to truncate Microsoft SQL Server transaction logs.Details: Failed to process 'TruncateSQLLog' command.
Failed to truncate SQL server transaction logs for instances: BI See guest helper log."
This normally means that the account used for Application Aware backup is not a member of the role "sysadmin" on the instance, but I check and it was "sysadmin". See Veeam KB: https://www.veeam.com/kb1746
So i looked at the log files on the machine that has the SQL database instance, C:\ProgramData\Veeam\Backup\VeeamGuestHelper_XXXXXXXXX.txt and found this:
16-09-2016 22:19:08 3528 Using account DOMAIN\Veeam_Service 16-09-2016 22:19:08 3528 Instance: BI 16-09-2016 22:19:08 3528 Database master: failed to truncate transaction logs. Error: Code = 0x80040e14 16-09-2016 22:19:08 3528 Code meaning = IDispatch error #3092 16-09-2016 22:19:08 3528 Source = Microsoft OLE DB Provider for SQL Server 16-09-2016 22:19:08 3528 Description = Cannot back up the log of the master database. Use BACKUP DATABASE instead. 16-09-2016 22:19:08 3528 Database tempdb: skipped transaction log truncation. 16-09-2016 22:19:08 3528 Database model: skipped transaction log truncation. 16-09-2016 22:19:08 3528 Database msdb: skipped transaction log truncation. 16-09-2016 22:19:08 3528 Database BI-PRODUCTION: transation logs have been truncated.
Then i looked at the "master" system database on the instance and found the the customers SQL DBA had change the "Recovery model" for that system database to "Full".
After changing the Recovery model to Simple, with also is the Microsoft default, the warning disappeared. So it look like Veeam does not support changing that.
The log files look like this after the change.
17-09-2016 11:43:44 2792 Using account DOMAIN\Veeam_Service 17-09-2016 11:43:44 2792 Instance: BI 17-09-2016 11:43:44 2792 Database master: skipped transaction log truncation. 17-09-2016 11:43:44 2792 Database tempdb: skipped transaction log truncation. 17-09-2016 11:43:44 2792 Database model: skipped transaction log truncation. 17-09-2016 11:43:44 2792 Database msdb: skipped transaction log truncation. 17-09-2016 11:43:44 2792 Database BI-PRODUCTION: transation logs have been truncated.
Update:
One of my colleague ask if we changed all the databases to simple, and we did not only change the "master" system database.
Here is a link to the default for the SQL system databases: https://technet.microsoft.com/en-us/library/ms365937(v=sql.105).aspx
Update:
This is now in the Veeam documentation here: https://helpcenter.veeam.com/docs/backup/explorers/vesql_considerations.html?ver=95
Hello,
master, model and msdb are not part off veeam backup, so they had to be set to “simple” because they are then excluded from application level backup and only part of the file (full) backup.
See Number 3:
https://helpcenter.veeam.com/docs/backup/explorers/vesql_considerations.html?ver=95
Hi,
Once you made sure all the required permission including adding the service account part of server local admins group. Also make sure the guest interaction proxy is (same subnet) of your SQL server. This is another trick you can use in order to truncate SQL logs correctly.
Regards,
Just wanted to let you know this was still helpful even in v12. Thanks for the information. We did have 1 DB that was set to full and caused this problem. Was resolved by marking it Simple.