- Veeam Support Knowledge Base
- Job reports warning "Failed to truncate transaction logs for SQL instances: Possible reasons: lack of permissions, or transaction log corruption."
Backup Job reports warning "Failed to truncate Microsoft SQL Server transaction logs."
Cheers for trusting us with the spot in your mailbox!
Now you’re less likely to miss what’s been brewing in our knowledge base with this weekly digest
Oops! Something went wrong.
Please, try again later.
Challenge
Backup Job with Application-Aware Processing enabled and configured to "process transaction logs" displays the warning:
-
Failed to truncate Microsoft SQL Server transaction logs. Details: Failed to process 'TruncateSQLLog' command. Failed to truncate SQL server transaction logs for instances: SQLINSTANCE. See guest helper log. .
-
Unable to truncate SQL server transaction logs. Details: Failed to truncate SQL server transaction logs for instances: MSSQLSERVER. Login failed for user 'DOMAIN\Bkpadmin'.
Solution
Confirm that the account used by Veeam has required permissions
- The account used by Veeam to perform truncation must have the required SQL permissions as outlined here.
- For Virtual Machines (vSphere/Hyper-v), the account is specified on the Guest Processing tab of the Backup Job.
- For machines backed up using Veeam Agent for Microsoft Windows, the account used for SQL Truncation is the account used to add the machine to the Protection Group.
- If SQL Truncation cannot be performed with the specified account, the software will failover and attempt to use the NT AUTHORITY\SYSTEM account.
Expand to view example of how to confirm permissions
- Identify the account the Backup job is trying to use:
- Connect to the SQL Instance on the machine that is failing to truncate and make sure the sysadmin role is assigned to the account used by Veeam.
Gather Logs and Contact Support
If the truncation process continues to fail after verifying that the account performing the truncation has the required SQL permissions, collect the logs as specified below according to the program in use and create a support case.
Veeam Backup & Replication - How to locate and collect VSS/VIX log files from Guest OS
Veeam Agent for Microsoft Windows - How to collect logs for Veeam Agent for Microsoft Windows
Advanced Troubleshooting
Review log files to identify the issue
To investigate why SQL log truncation has failed, the Veeam logs will need to be reviewed to determine which part of the truncation procedure failed.
For Veeam Backup & Replication, the log file containing information about the truncation procedure will be found on the SQL server, and is named:
C:\ProgramData\Veeam\Backup\VeeamGuestHelper_ddmmyyyy.log
Within the VeeamGuestHelper log file, beneath the line "Transaction log truncation statistics," you will see information about which account was used, which SQL instances were processed, and a list of each database that was interacted with.
VeeamGuestHelper Log Example
Transaction log truncation statistics.
Using account domain\bkpadmin
Instance: SQLINSTANCE Failed. Error: Code = 0x80040e4d
Code meaning = IDispatch error #3149
Source = Microsoft OLE DB Driver for SQL Server
Description = Login failed for user 'DOMAIN\bkpadmin'.
COM error: Code: 0x80040e4d
Using account NT AUTHORITY\SYSTEM
Instance: SQLINSTANCE
Database master: skipped transaction log truncation.
Database tempdb: skipped transaction log truncation.
Database model: failed to truncate transaction logs. Error: Code = 0x80040e14
Code meaning = IDispatch error #3092
Source = Microsoft OLE DB Driver for SQL Server
Description = The server principal "NT AUTHORITY\SYSTEM" is not able to access the database "model" under the current security context.
Database msdb: skipped transaction log truncation.
Database EDSHSD: failed to truncate transaction logs. Error: Code = 0x80040e14
Code meaning = IDispatch error #3092
Source = Microsoft OLE DB Driver for SQL Server
Description = The server principal "NT AUTHORITY\SYSTEM" is not able to access the database "DBNAME" under the current security context.
Enumerating SQL instances. Failed.
INFO RPC: truncation SQL logs.. Failed.
ERR BlobCall() error.
ERR Failed to process 'TruncateSQLLog' command.
ERR Failed to truncate SQL server transaction logs for instances: SQLINSTANCE. See guest helper log. . For Veeam Agent for Microsoft Windows, the log file containing truncation details will be found on the SQL server in:
C:\ProgramData\Veeam\Endpoint\<jobname>\Job.Backup.log
Within the Job.Backup.log log file, beneath the line "Checking if can truncate SQL logs." you will see information about which account was used, which SQL instances were processed, and a list of each database that was interacted with.
Job.Backup.log Example
Checking if can truncate SQL logs. Cluster ObjectId: 00000000-0000-0000-0000-000000000000
[GuestDbBackupLocker] Using CLocalItemReadWriteLockService
VSSControl: Truncating SQL logs. Creds specified: yes. Username: DOMAIN\Bkpadmin.
Enumerating SQL instances
Trying to connect to Microsoft Failover Cluster
Trying to connect to Microsoft Failover Cluster
Enumerating Microsoft SQL Server instances by service names.
SQL instance found: []
Enumerating Microsoft SQL Server instances by service names.. Ok.
Truncating database logs (SQL instance: ). User: DOMAIN\Bkpadmin.
Connecting to mssql, connection string: Provider='msoledbsql';Data Source='(local)';Integrated Security='SSPI';Persist Security Info=False, timeout: 15
Code = 0x80040e4d
Code meaning = IDispatch error #3149
Source = Microsoft OLE DB Driver for SQL Server
Description = Login failed for user 'DOMAIN\Bkpadmin'.
Failed to truncate SQL logs.
Failed to truncate SQL server transaction logs for instances: MSSQLSERVER. Login failed for user 'DOMAIN\Bkpadmin'.Known Errors and Solutions
Below is a list of commonly observed errors found in the logs and solutions to each.
Description = Login failed for user 'DOMAIN\user'.The user listed in the error cannot connect to the SQL Instance, as it does not have the required SQL permissions.
This specific error means that the account in the error isn't listed in the "Logins" section of the SQL Instance, and therefore has no right to login to the SQL Instance. To resolve this, create a login for the account within the SQL Instance on the machine that is failing to truncate, and assign it the sysadmin role.
Note: You will need to launch SQL Management Studio using an account other than the one in the error, as that account will not have permission to connect to the SQL Instance.
Description = The server principal "DOMAIN\user" is not able to access the database "DBName" under the current security context.The user listed in the error is able to log in to the SQL Instance but lacks the required SQL permissions to interact with the databases.
To resolve this, connect to the SQL Instance of the machine that is failing to truncate, and edit the login entry ( Security > Logins ), and assign the sysadmin role.
Note: You will need to launch SQL Management Studio using an account other than the one in the error, as that account will not have sufficient permissions to assign itself the sysadmin role.
BACKUP detected corruption in the database log. Check the error log for more information.BACKUP LOG cannot be performed because there is no current database backup.The issue occurs when the secondary node has always been backed up with the "copy only" flag, and its standalone DBs do not have a full backup. Thus during the truncation of the standalone DB logs, this error occurs.
The same solution applies if you get this message about the excluded vCenter or Veeam database.
Description = Query timeout expiredThis message means that the truncation process could not be completed within the default execution timeout.
It is possible to increase the execution timeout. However, the registry value and placement depend on the SQL backup job's configuration. See below:
For a VM being backed up by Veeam Backup & Replication
The following registry value can be created on the SQL server having the issue and adjusted to change the SQL task execution timeout (default 300 seconds).
Key Location: HKLM\SOFTWARE\Veeam\Veeam Backup and Replication
Value Name: SqlExecTimeout
Value Type: DWORD (32-Bit) Value
Default Value Data: 300 (Decimal)
The value is in seconds. Increase the value and rerun the job. The value needed will be unique to each environment.
A common tactic is to increase the value by 300, test the job, and increase by another 300 seconds if it fails.
For a machine backed up using Veeam Agent for Microsoft Windows *except Failover Cluster job type
This registry value may be used for all jobs involving Veeam Agent for Microsoft Windows, except Failover Cluster type jobs.
The following registry value can be created on the SQL server having the issue and adjusted to change the SQL task execution timeout (default 300 seconds).
Key Location: HKLM\SOFTWARE\Veeam\Veeam Endpoint Backup
Value Name: SqlExecTimeout
Value Type: DWORD (32-Bit) Value
Default Value Data: 300 (Decimal)
The value is in seconds. Increase the value and rerun the job. The value needed will be unique to each environment.
A common tactic is to increase the value by 300, test the job, and increase by another 300 seconds if it fails.
For a SQL Failover Cluster that is backed up using Failover Cluster job within Veeam Backup & Replication
This scenario is specific to when a Veeam Agent for Microsoft Windows backup is managed by Veeam Backup & Replication, and the Job Mode is set to Failover cluster type.
To implement this SQL task execution timeout, the following registry value must be created on all nodes of the SQL Failover cluster.
Key Location: HKLM\SOFTWARE\Veeam\Veeam Backup and Replication
Value Name: SqlExecTimeout
Value Type: DWORD (32-Bit) Value
Default Value Data: 300 (Decimal)
The value is in seconds. Increase the value and rerun the job. The value needed will be unique to each environment.
A common tactic is to increase the value by 300, test the job, and increase it by another 300 seconds if it fails.
If this KB article did not resolve your issue or you need further assistance with Veeam software, please create a Veeam Support Case.
To submit feedback regarding this article, please click this link: Send Article Feedback
To report a typo on this page, highlight the typo with your mouse and press CTRL + Enter.
