Avamar: SQL - What causes "log gap" errors when using Avamar SQL Plugin to backup Databases

There are two (2) workarounds to address this issue:

• Modify the SQL Dataset and "Enable Force Full" option
• Add in avsql.cmd the following flag

--forcefull=true

• Create a avsql.cmd file on the C:\Program Files\avs\var folder if it does not exist.

Find an excerpt from avsql log that shows the Log Sequence Number (LSN). This LSN is being retrieved from sqlmeta.xml file and compare to the last_backup_lsn for the Database from sys.database_recovery_status table:

2019/10/22-19:45:45.51399 [avsql_assist] database 'TEST', last backup lsn = '53119000001811800001'
2019/10/22-19:45:45.51500 [avsql_assist] avsql_metadata::get
2019/10/22-19:45:45.51699 [avsql_assist] ===> avsql_metadata::get
2019/10/22-19:45:45.51799 [avsql_assist] avsql_assist::align_numeric_ustrings
2019/10/22-19:45:45.52200 [avsql_assist] Before alignment - Str1: '53119000001801200001', Str2: '53119000001811800001'
2019/10/22-19:45:45.52300 [avsql_assist] After alignment - Str1: '53119000001801200001', Str2: '53119000001811800001'
2019/10/22-19:45:45.52399 [avsql_assist] sqlconnectimpl_smo::get_last_backup_lsn
2019/10/22-19:45:45.52699 [avsql_assist] retrieving last backup lsn for 'TEST' db from sys.database_recovery_status
2019/10/22-19:45:45.52800 [avsql_assist] ===> sqlconnectimpl_smo::InitDll
2019/10/22-19:45:45.52900 [avsql_assist] SMO dll already loaded.
2019/10/22-19:45:45.52900 [avsql_assist] SMOWrap::SMO_GetLastBackupLSN
2019/10/22-19:45:45.53600 [avsql_assist] database 'TEST', last backup lsn = '53119000001811800001'
2019/10/22-19:45:45.53600 [avsql_assist] avsql_assist::align_numeric_ustrings
2019/10/22-19:45:45.53800 [avsql_assist] Before alignment - Str1: '53119000001801200001', Str2: '53119000001811800001'
2019/10/22-19:45:45.53900 [avsql_assist] After alignment - Str1: '53119000001801200001', Str2: '53119000001811800001'
2019/10/22-19:45:45.53999 [avsql_assist] : A log gap was identified or a full backup was not found.

SQL query to get LSN number for the Database TEST from sys.database_recovery_status is:

SELECT last_log_backup_lsn FROM sys.database_recovery_status "WHERE database_id = DB_ID(N'TEST')"

The SQL query to update sqlmeta.xml file for this Database is:

SELECT top 1 last_lsn as last_log_backup_lsn FROM msdb..backupset WHERE database_name=N'TEST'" "AND type LIKE 'L' ORDER by last_lsn DESC;

When troubleshooting log gap errors, it is required to enable debug by adding in avsql.cmd the following flags. This should be done to observe the LSN information logged on the Avamar SQL logs.

--debug
--verbose=5
--x01=131072
--x14=3276

Create a avsql.cmd file on the C:\Program Files\avs\var folder if it does not exist.

Also run the above 2 SQL queries to mimic Avamar behavior from query analyzer in SQL management studio and validate SQL returned LSN numbers.

Related Knowledge Base Articles:
 

• Avamar: SQL plug-in - the second Incremental Backup is Promoted to FULL without a valid reason  
• Avamar SQL Backup completed with exception: Force Incremental after full is failing with the error: "Error: A log gap was identified, or a full backup was not found".
• Avamar: SQL Backup is failing with the error: "A log gap was identified, or a full backup was not found"
• Avamar SQL Cluster backups failed with log gap and sqlmeta.xml file
• Avamar AlwaysOn SQL incremental backups randomly fail due to "log gap" errors