Avamar: SQL backups failing due to poor Database Consistency Check performance

• Consistency check is enabled for all SQL backup types (Full, Incremental, and Differential):

<flag type="checkbox" pidnum="3006" value="true" name="consistency-check" /> 

• Incremental backups with a 1-hour backup window consistently fail to complete within the allotted schedule.
• Full backups take over 2 hours to complete.
• Without consistency-check enabled:
  • Full backups complete in ~12 minutes.
  • Incremental and transaction log backups complete in 2–3 minutes.

The Avamar avsql plug-in performs Database Consistency Checks sequentially on individual databases, resulting in suboptimal performance.

1. The system enqueues the database task with status ENQUEUED.
2. A thread dequeues the task and starts the consistency check.
3. After the check, backup streams are spawned for that database.
4. The system waits for all streams to start before enqueuing the next database task.
5. Steps 2–4 repeat for each database

This serialized approach delays the stream initiation and contributes to timeouts.

If the consistency check takes a long time, then disable the SQL consistency check within Avamar Dataset. This should be done especially for Incremental backups. 

<flag type="checkbox" pidnum="3006" value="false" name="consistency-check" />

Run it manually from SQL Management Studio as following query:

DBCC CHECKDB ('database_name')

Useful options are:

DBCC CHECKDB database_name
DBCC CHECKALLOC database_name
DBCC TEXTALL database_name
DBCC CHECKCATALOG database_name

More details can be found at: https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-checkdb-transact-sql?view=sql-server-ver15 (External Link)