SQL backups fail with snapshot error PHOENIX54
Problem description
SQL backups fail with snapshot error PHOENIX54 if the path to the SQL database contains a double slash or an extra space at the end.
Causes
Scenario 1:
Phoenix checks the database paths before backup to ensure snapshot consistency. If the database path has a double slash or an extra space at the end, the snapshot creation fails. Databases created with the CREATE DATABASE command generally exhibit this behavior. The path to the data files and log files includes an extra backslash before the filename. For example, M:\MSSQL\Data\\testbb.mdf. The file names with the extra backslash are written to the system databases. The extra backslash is visible in the database properties screen.
Scenario 2:
Whenever backup is triggered, SQL writer goes into a failed state due to insufficient log space.
Writer name: 'SqlServerWriter'
Writer Id: {a65faa63-5ea8-4ebc-9dbd-a0c4db26912a}
Writer Instance Id: {a5854dc7-3a3d-4187-90d8-457bb2ba5ae8}
State: [8] Failed
Last error: Non-retryable error
Log records that are not managed correctly will eventually fill up the disk causing no more modifications to the database.
Transaction log growth can occur for a few different reasons: Long running transactions, incorrect recovery model configuration and lack of log backups can grow the log.
Traceback
Phoenix log location: PhoenixLogs-Job<jobid>\<backupset>\PhoenixJob<jobid>\Phoenix.<timestamp>
[ERROR] <_MainThread(MainThread)> Error <class 'inSyncLib.inSyncError.SyncError'>:Error occurred while getting file size for file: 'VishalSarode.mdf' from snapshot (#100040007) (Error Code : VSS7).
[ERROR] <_MainThread(MainThread)> Error <class 'inSyncLib.inSyncError.SyncError'>:Failed to take snapshot. (#100000036) (Error Code : PHOENIX54).
[2022-10-12 16:29:46,760] [ERROR] <_MainThread(MainThread)> Error <class 'inSyncLib.inSyncError.SyncError'>:VSS writer has reported failure : [state = 8, result_code = -2147212300], writer details are = [writer class id : {a65faa63-5ea8-4ebc-9dbd-a0c4db26912a}, writer name : SqlServerWriter] (#100040001) (Error Code : VSS1). Traceback -Traceback (most recent call last):
File "agents\sqlserver\VssHelper.pyc", line 275, in take_snapshot
File "agents\sqlserver\sql_vss_2.pyc", line 573, in take_snapshot
File "roboClientLib\vssutils.pyc", line 453, in take_snapshot
File "roboClientLib\vssutils.pyc", line 421, in check_writer_status
File "inSyncLib\inSyncError.pyc", line 32, in raise_error
SyncError: VSS writer has reported failure : [state = 8, result_code = -2147212300], writer details are = [writer class id : {a65faa63-5ea8-4ebc-9dbd-a0c4db26912a}, writer name : SqlServerWriter] (#100040001) (Error Code : VSS1)
[2022-10-12 16:29:47,058] [ERROR] <_MainThread(MainThread)> Error <class 'inSyncLib.inSyncError.SyncError'>:Failed to take snapshot. (#100000036) (Error Code : PHOENIX54). Traceback -Traceback (most recent call last):
File "agentPartnerProcess.pyc", line 93, in _process_request_start
File "agents\sqlserver\SqlAgent.pyc", line 68, in process_backup_start
File "agents\sqlserver\SqlBackup.pyc", line 405, in estimate
File "agents\sqlserver\SqlDataManager.pyc", line 615, in estimate
File "agents\sqlserver\SqlDataManager.pyc", line 214, in estimate
File "roboClientLib\phoenixApi.pyc", line 2772, in get_estimate
File "roboClientLib\phoenixApi.pyc", line 2631, in __create_snapshot
File "agents\sqlserver\SqlHelper.pyc", line 32, in get_snapmap
File "agents\sqlserver\VssHelper.pyc", line 297, in take_snapshot
SyncError: Failed to take snapshot. (#100000036) (Error Code : PHOENIX54)
Event Viewer Log
Sqllib error: OLEDB Error encountered calling ICommandText::Execute. hr = 0x80040e14. SQLSTATE: 42000, Native Error: 3013
Error state: 1, Severity: 16
Source: Microsoft SQL Server Native Client 11.0
Error message: BACKUP DATABASE is terminating abnormally.
SQLSTATE: 42000, Native Error: 3201
Error state: 7, Severity: 16
Source: Microsoft SQL Server Native Client 11.0
Error message: Cannot open backup device '{0DE40922-1E27-433B-A0F4-0F1E370A242F}13'. Operating system error 0x80070002(The system cannot find the file specified.).
Verification
- Right click the database, choose Tasks-> Shrink-> Files:
- On the Shrink File window, change the File Type to Log.
- You can also verify by triggering a native backup and check if it is successful.
- In this case, it gives the following error-
Resolution
-
Open SQL Server Management Studio. Ensure that the version of the SQL Server Management Studio matches the version of the SQL instance running on the machine.
-
Run either of the following queries to get a list of database names and their locations.
USE <database_name>
SELECT * FROM sys.database_files
OR
USE <database_name>
SELECT name, physical_name from sys.database_files -
In the query results, determine the database whose physical name has a double slash /space in the physical_name column.
-
Execute the following command to modify physical path to the file
ALTER DATABASE <database name>MODIFY FILE (NAME = '<logical name>', FILENAME='<path to file>');
where
<database name> is the name of the database that is mentioned in the error, no changes needed
<logical name> is the logical name of the file mentioned in the error, no changes needed
<path to file> is the full path to the file that is mentioned in the error, without double slashes or extra spaces at the end
-
Request to consult with your DBA and then run the following command to shrink the log files-
ALTER DATABASE <database name>
SET RECOVERY SIMPLE;
GO
-- Shrink the truncated log file to 1 MB.
DBCC SHRINKFILE ('<logical name>', 1);
GO
-- Reset the database recovery model.
ALTER DATABASE <database name>
SET RECOVERY FULL;where,
<database name> is the name of the database in which log space is full
<logical name> is the logical name of the log file
Workaround
If the steps given in the Solution, to fix the issue will take time then meanwhile workaround can be followed. Uncheck the affected database from the SQL content rule which has issues and save the backupset, so that backups of the rest of the DB’s are successfully protected.