Problem with fix SB-4046 and multiple output backup files

ChrisAVWoodChrisAVWood Posts: 361
edited September 19, 2011 9:57PM in SQL Backup Previous Versions
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
English DBA living in CANADA
· Share on Twitter

Comments

  • peteypetey Posts: 2,358 New member
    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
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
    · Share on Twitter
  • ChrisAVWoodChrisAVWood Posts: 361
    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
    English DBA living in CANADA
    · Share on Twitter
  • peteypetey Posts: 2,358 New member
    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.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
    · Share on Twitter
  • ChrisAVWoodChrisAVWood Posts: 361
    Great.

    I will try and test this out first.

    Thanks

    Chris
    English DBA living in CANADA
    · Share on Twitter
  • ChrisAVWoodChrisAVWood Posts: 361
    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
    English DBA living in CANADA
    · Share on Twitter
  • ChrisAVWoodChrisAVWood Posts: 361
    I have now tested this out and its works as advertised.

    Thanks

    Chris
    English DBA living in CANADA
    · Share on Twitter
  • peteypetey Posts: 2,358 New member
    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.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
    · Share on Twitter
Sign In or Register to comment.