Veeam Backup warning “Unable to truncate Microsoft SQL Server transaction logs.”

September 16, 2016 3 By Allan Kjaer

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

Please share this page if you find it usefull: