Problem with fix SB-4046 and multiple output backup files

ChrisAVWood

Hi,

I was pleased when SB-4046 was finally introduced as part of 6.5. It deleted the backup file when a backup failed because it ran out of disk space. We used to have many partial backups on disk because of this and that caused other backup jobs to fail for the same reason and we had to do a cleanup.

Now we have started to run our backups to 2 backup files for some larger databases. This morning we had disk space problems on two servers that create split backups. One deleted the output that was being written to, leaving one partial backup and the other job actually deleted both of the backup files. I am including the logs for both jobs with the first the successfull one.

Successfull failure

SQL Backup log file 6.5.1.9

-SQL "BACKUP DATABASE [XXX_SYST] TO DISK = 'G:\APPLBKUP\XXX\SYST\FULL\<AUTO>.sq6' WITH COMPRESSION = 2, FILECOUNT = 2, DISKRETRYINTERVAL = 30, DISKRETRYCOUNT = 1, ERASEFILES = 22h, VERIFY "

---

ERRORS AND WARNINGS

---

2011/08/31 2:28:00 AM: Backing up XXX_SYST (full database) to:
2011/08/31 2:28:00 AM: G:\APPLBKUP\XXX\SYST\FULL\FULL_XXX_SYST_20110831_022800_01.sq6
2011/08/31 2:28:00 AM: G:\APPLBKUP\XXX\SYST\FULL\FULL_XXX_SYST_20110831_022800_02.sq6

2011/08/31 2:28:00 AM: BACKUP DATABASE [XXX_SYST] TO VIRTUAL_DEVICE = 'SQLBACKUP_237323D0-6596-41E2-9C32-F422F3AEE65D', VIRTUAL_DEVICE = 'SQLBACKUP_237323D0-6596-41E2-9C32-F422F3AEE65D01' WITH BUFFERCOUNT = 12, BLOCKSIZE = 65536, MAXTRANSFERSIZE =
1048576, NAME = N'Database (XXX_SYST), 2011/08/31 2:28:00 AM', DESCRIPTION = N'Backup on 2011/08/31 2:28:00 AM Server: YYYYYYY Database: XXX_SYST', FORMAT

2011/08/31 3:40:59 AM: Thread 0 error:
Process terminated unexpectedly. Error code: -2139684860 (An abort request is preventing anything except termination actions.)
Error 620: Error writing to backup file(s).
Error 620: Error writing to backup file(s).
Process terminated unexpectedly. Error code: -2139684860 (An abort request is preventing anything except termination actions.)
2011/08/31 3:40:59 AM: Warning 210: Thread 0 warning:
WriteFile failed for file: G:\APPLBKUP\XXX\SYST\FULL\FULL_XXX_SYST_20110831_022800_01.sq6 at position: 8967422976
2011/08/31 3:40:59 AM: WriteFile failed for file: G:\APPLBKUP\XXX\SYST\FULL\FULL_XXX_SYST_20110831_022800_01.sq6 (There is not enough space on the disk.)
2011/08/31 3:40:59 AM:
2011/08/31 3:40:59 AM: Thread 1 error:
Error 620: Error writing to backup file(s).
Process terminated unexpectedly. Error code: -2139684860 (An abort request is preventing anything except termination actions.)
2011/08/31 3:40:59 AM: Warning 210: Thread 1 warning:
WriteFile failed for file: G:\APPLBKUP\XXX\SYST\FULL\FULL_XXX_SYST_20110831_022800_02.sq6 at position: 8885634048
2011/08/31 3:40:59 AM: WriteFile failed for file: G:\APPLBKUP\XXX\SYST\FULL\FULL_XXX_SYST_20110831_022800_02.sq6 (There is not enough space on the disk.)
2011/08/31 3:40:59 AM:
2011/08/31 3:40:59 AM: SQL error 3013: SQL error 3013: BACKUP DATABASE is terminating abnormally.
2011/08/31 3:40:59 AM: SQL error 3271: SQL error 3271: A nonrecoverable I/O error occurred on file "SQLBACKUP_237323D0-6596-41E2-9C32-F422F3AEE65D:" 995(error not found).
2011/08/31 3:40:59 AM: SQL error 3202: SQL error 3202: Write on "SQLBACKUP_237323D0-6596-41E2-9C32-F422F3AEE65D01" failed: 1117(error not found)

Unsuccessfull failure

SQL Backup log file 6.5.1.9

-SQL "BACKUP DATABASE [XXX_MANT] TO DISK = 'G:\APPLBKUP\XXX\MANT\FULL\<AUTO>.sq6' WITH COMPRESSION = 2, FILECOUNT = 2, DISKRETRYINTERVAL = 30, DISKRETRYCOUNT = 1, ERASEFILES = 46h, VERIFY "

---

ERRORS AND WARNINGS

---

2011/08/31 2:27:00 AM: Backing up XXX_MANT (full database) to:
2011/08/31 2:27:00 AM: G:\APPLBKUP\XXX\MANT\FULL\FULL_XXX_MANT_20110831_022700_01.sq6
2011/08/31 2:27:00 AM: G:\APPLBKUP\XXX\MANT\FULL\FULL_XXX_MANT_20110831_022700_02.sq6

2011/08/31 2:27:01 AM: BACKUP DATABASE [XXX_MANT] TO VIRTUAL_DEVICE = 'SQLBACKUP_26878968-BE7E-480D-BCD2-2F982AA825BB', VIRTUAL_DEVICE = 'SQLBACKUP_26878968-BE7E-480D-BCD2-2F982AA825BB01' WITH BUFFERCOUNT = 12, BLOCKSIZE = 65536, MAXTRANSFERSIZE =
1048576, NAME = N'Database (XXX_MANT), 2011/08/31 2:27:00 AM', DESCRIPTION = N'Backup on 2011/08/31 2:27:00 AM Server: ZZZZZZZ Database: XXX_MANT', FORMAT

2011/08/31 3:53:38 AM: Thread 0 error:
Process terminated unexpectedly. Error code: -2139684860 (An abort request is preventing anything except termination actions.)
Process terminated unexpectedly. Error code: -2139684860 (An abort request is preventing anything except termination actions.)
2011/08/31 3:53:38 AM:
2011/08/31 3:53:38 AM: Thread 1 error:
Error 620: Error writing to backup file(s).
Process terminated unexpectedly. Error code: -2139684860 (An abort request is preventing anything except termination actions.)
2011/08/31 3:53:38 AM: Warning 210: Thread 1 warning:
WriteFile failed for file: G:\APPLBKUP\XXX\MANT\FULL\FULL_XXX_MANT_20110831_022700_02.sq6 at position: 6960448512
2011/08/31 3:53:38 AM: WriteFile failed for file: G:\APPLBKUP\XXX\MANT\FULL\FULL_XXX_MANT_20110831_022700_02.sq6 (There is not enough space on the disk.)
2011/08/31 3:53:38 AM:
2011/08/31 3:53:38 AM: SQL error 3013: SQL error 3013: BACKUP DATABASE is terminating abnormally.
2011/08/31 3:53:38 AM: SQL error 3271: SQL error 3271: A nonrecoverable I/O error occurred on file "SQLBACKUP_26878968-BE7E-480D-BCD2-2F982AA825BB:" 995(error not found).
2011/08/31 3:53:38 AM: SQL error 3202: SQL error 3202: Write on "SQLBACKUP_26878968-BE7E-480D-BCD2-2F982AA825BB

Looks like I would need this patch further refined so I don't have one or more partial backup files left on disk if disk space becomes an issue.

Thanks

Chris

petey

Thanks for reporting the error. This has been fixed in a patch release (6.5.2.9), which you can download using the following URL if you need it:

ftp://support.red-gate.com/Patches/sql_ ... _5_2_9.zip

ChrisAVWood

Pete,

I downloaded the zip file and it just includes a new SQBCoreService.exe and SQLBackupC.exe files. To implement this I would need to copy this over the current 6.5.1.9 and maybe the SQLBackupC.exe too?

Will this be included in v 7 when it becomes available?

Thanks

Chris

petey

Sorry, forgot to provide the instructions to use the files in the archive:

To replace the existing service executable file (SQBCoreService.exe), do the following:

- ensure that no SQL Backup processes are running
- stop the SQL Backup Agent service, or disable the cluster resource if on a cluster
- rename the existing executable file (SQBCoreService.exe) in the SQL Backup installation folder
- extract and place the patched executable file into the same folder
- restart the SQL Backup Agent service/cluster resource

For the command line interface executable file (SQLBackupC.exe), you can just copy over the existing file.

Yes, this will be included in v7.

ChrisAVWood

Great.

I will try and test this out first.

Thanks

Chris

ChrisAVWood

I have now copied the executable to a test server. In the GUI under Help/About it shows 6.5.1.9. Under Edit/Properties it shows SQL Backup version as 6.5.1.9 and Service Application Version as 6.5.2.9. In any job show log entry its 6.5.2.9. I guess from all of this I am now using 6.5.2.9 to take backups.

Chris

ChrisAVWood

I have now tested this out and its works as advertised.

Thanks

Chris

petey

Thanks for the update.

Re. the version numbers, 6.5.1.9 would be the version of your SQL Backup GUI, and also the version of your SQL Backup extended stored procedure. 6.5.2.9 would be the version of the SQL Backup Agent service. This configuration is fine, as the changes in the Agent service does not have any impact on the GUI nor the extended stored procedure.