SQL Server Log Chain Breaks in Transaction Log Validated Sync (KBA9572)

Issue

When using validated sync with SQL Server virtualization transaction logs, certain events occur causing the log chain to break, such as the inability to find a particular log file to restore on the staging database. When this occurs, Delphix throws the following fault:

  * Object ID                     FAULT-344
  Fault Id                        344
  Target                          MSSQL_LINKED_SOURCE-21
  Target Name                     dSource_Target
  Bundle Id                       fault.mssql.source.logchainbreak
  Params                          99999999-3BBB-4EEE-9444-666666666666
                                  77777777-1111-2222-939C-588888893A59
                                  dSource_Target
  Title                           Source database has had a log chain break
  Description                     Failed to ingest the transaction log backup with UUID "99999999-3BBB-4EEE-9444-666666666666" over the last restored backup with UUID "77777777-1111-2222-939C-588888893A59" due to a log chain break on the source database "dSource_Target". Restore from a full or differential database backup is needed to continue.
  Response                        Validated sync will be stopped and no transaction log backups will be ingested.
  Action (Action)                 Perform a sync on the dSource to restore a full or differential database backup.
  Severity                        CRITICAL
  Status                          ACTIVE
  Date Diagnosed                  2022-09-21 19:55:34 UTC
  Date Resolved
  Comments
  Posted By                       UNKNOWN

The suggested action implies a sync of the staging database of the dSource will resolve the issue.

Common reasons why it happens:

• Switching the Recovery Modes from FULL to SIMPLE.
• Log Backups using the Truncate Only option (discontinued in SQL Server 2008).
• Log Backup with No_log Option - Read more on BACKUP LOG WITH NO_LOG  (discontinued in SQL Server 2008).
• Reverting database with Database Snapshot.

Applicable Delphix Versions

Click here to view the versions of the Delphix engine to which this article applies

Major Release	All Sub Releases
6.0	6.0.0.0, 6.0.1.0, 6.0.1.1, 6.0.2.0, 6.0.2.1, 6.0.3.0, 6.0.3.1, 6.0.4.0, 6.0.4.1, 6.0.4.2, 6.0.5.0, 6.0.6.0, 6.0.6.1, 6.0.7.0, 6.0.8.0, 6.0.8.1, 6.0.9.0, 6.0.10.0, 6.0.10.1, 6.0.11.0, 6.0.12.0, 6.0.12.1, 6.0.13.0, 6.0.13.1, 6.0.14.0, 6.0.15.0, 6.0.16.0
5.3	5.3.0.0, 5.3.0.1, 5.3.0.2, 5.3.0.3, 5.3.1.0, 5.3.1.1, 5.3.1.2, 5.3.2.0, 5.3.3.0, 5.3.3.1, 5.3.4.0, 5.3.5.0, 5.3.6.0, 5.3.7.0, 5.3.7.1, 5.3.8.0, 5.3.8.1, 5.3.9.0
5.2	5.2.2.0, 5.2.2.1, 5.2.3.0, 5.2.4.0, 5.2.5.0, 5.2.5.1, 5.2.6.0, 5.2.6.1
5.1	5.1.0.0, 5.1.1.0, 5.1.2.0, 5.1.3.0, 5.1.4.0, 5.1.5.0, 5.1.5.1, 5.1.6.0, 5.1.7.0, 5.1.8.0, 5.1.8.1, 5.1.9.0, 5.1.10.0
5.0	5.0.1.0, 5.0.1.1, 5.0.2.0, 5.0.2.1, 5.0.2.2, 5.0.2.3, 5.0.3.0, 5.0.3.1, 5.0.4.0, 5.0.4.1, 5.0.5.0, 5.0.5.1, 5.0.5.2, 5.0.5.3, 5.0.5.4

Resolution

As the suggested action says:

Perform a sync on the dSource to restore a full or differential database backup.

Manually run the SnapSync on the dSource selecting either of the following two options in the UI:

 

Select either Use the most recent full or differential backup or Use a specific full or differential backup.

The Use the most recent full or differential backup option will result in discovering the most recent full/diff backup and proceed with ingestion. When completed, subsequent transaction logs will be ingested.

The Use a specific full or differential backup option requires the DBA to determine the backup UUID of a full or differential backup from the source instance backupset history. This value can be entered in the Backup Set UUID field:

 

Once ingestion completes, the validated sync will resume. You will likely see a "gap" in timecards depending on how far out the full/diff backup is from the log chain break.

Troubleshooting

A DBA can check SQL Server activities to check for conditions that can cause a break.

 

---