Problem description
SQL backup fails on MS-SQL Server running on Windows
The error observed during backup:
Error: failed to start upload on sql-ioclient
Additional logs: Error while creating Snapshot
This issue can be reproduced when a SQL Server database transaction log is full, preventing snapshot creation and data upload.
Cause
The issue is caused by a full transaction log file (.ldf) in one of the databases. SQL Server restricts write operations, including backups, when the transaction log cannot accommodate additional entries.
Traceback
PhoenixSQLAgent.log:
level=error ts=2024-11-06T02:17:25.567565Z filename=backup_agent.go:693 layer=backup message="Error while uploading backup for Job: 2035235" Error="failed to start upload on sql-ioclient: failed to start upload: failed to start
Resolution
To resolve the issue, clear space in the transaction log using one of the following methods:
Transaction Log Backup (For Full/Bulk-Logged Recovery Models):
BACKUP LOG [DatabaseName] TO DISK = 'E:\Backups\DatabaseName_Log.trn';
Purpose:Backs up the transaction log (required if the database is in Full or Bulk-Logged recovery model).
Truncates the inactive portion of the log, freeing up space inside the log file (though not reducing the physical file size).
Impact:
✅ Pros:
Safely clears log space without data loss.
Maintains point-in-time recovery capability.
⚠️ Cons:
Does not shrink the log file size on disk, it only marks portions as reusable.
Still need regular transaction log backups in the schedule to avoid future growth.
Shrink Log File(Temporary solution):
USE [DatabaseName];DBCC SHRINKFILE ([LogicalLogFileName], 1);Purpose:
Reduces the physical size of the log file on disk.
Useful if the file grew abnormally large due to a long-running transaction or missing log backups.
Impact:
✅ Pros:
Immediately reclaims disk space.
Temporary relief when disk space is critical.
⚠️ Cons:
Shrinking logs frequently causes fragmentation and forces SQL Server to later grow the log file again, which can impact performance.
Not recommended as a regular practice.
Should only be used after truncation (via a log backup or recovery model change).
Change Recovery Model (If permitted)
ALTER DATABASE [DatabaseName] SET RECOVERY SIMPLE;DBCC SHRINKFILE ([LogicalLogFileName], 1);Purpose:
Switches database to Simple Recovery Model.
This automatically truncates the transaction log after each checkpoint.
Then shrink physically reduces the file size.
Impact:
✅ Pros:
Quickly frees log space without requiring log backups.
Effective if log backups are not required (like in dev/test environments).
⚠️ Cons:
Breaks the log backup chain (you lose the ability for point-in-time recovery).
Switching back to Full requires a new full backup before log backups can resume.
Not suitable for production systems where point-in-time recovery is needed.
Once the space issue is resolved, Trigger the manual Differential backup.
Verification
1. Use DBCC SQLPERF(logspace) in SSMS to verify log space usage.
2. Confirm successful completion of the backup job from the Druva Console.
2. Monitor Event Viewer and SQL Server Logs for reoccurrences.