Competition: What’s your favorite Redgate tool? Enter now.

SQL Backup Exit Code does not reset with Successful Retry

DWolford1234DWolford1234 Posts: 44
edited July 27, 2006 12:16PM in SQL Backup Previous Versions
Hello,
I am experiencing the same problems as others have in that I have a small block of contiguous free memory. The new version of SQL Backup (I downloaded and installed V4.5.0.704 two days ago), retries backups at a lower maxtranfersize each time. That's great, and eventually, it does successfully backup. However, the SQL Backup Exit Code returns as 300, which was set by the earlier failures. Since I have my backup process embedded into my own routine, I use the exit code and sql error code to verify a successful backup has happened. With an exit code of 300, it is assumed that any file created is bad and it is deleted and the job fails. I worked around this issue a couple of days ago by hardcoding a MAXTRANSFERSIZE = 458752, and the backups worked fine since they didn't need to retry anymore. Unfortunately, my amount of contiguous free memopry appears to vary widely, so I would like to take advantage of the autoretry. I now I could just specify the lowest maxtransfersize, but I would prefer to use the greatest value possible.
Could you modify SQL Backup to return the last and correct Sql Backup exit code?

Here is what currently happens:

SQL Backup log file
7/27/2006 9:50:54 AM: Backing up dbAdmin (full database) to:
E:\SQLBACKUP\Database\dbAdmin\dbAdmin_FULL_20060727094944.sqb

7/27/2006 9:50:54 AM: BACKUP DATABASE [dbAdmin] TO DISK = 'E:\SQLBACKUP\Database\dbAdmin\dbAdmin_FULL_20060727094944.sqb' WITH NAME = '<AUTO>', DESCRIPTION = '<AUTO>', COMPRESSION = 3

7/27/2006 9:50:55 AM: VDI error 1010: Failed to get configuration from server. Check that the SQL Server instance is running, and that you have the SQL Server Systems Administrator server role. Error code: (-2139684860: An abort request is preventing anything except termination actions.)
7/27/2006 9:50:56 AM: SQL error 3013: BACKUP DATABASE is terminating abnormally.
7/27/2006 9:50:56 AM:
7/27/2006 9:50:56 AM: Memory profile
7/27/2006 9:50:56 AM: Type Maximum Minimum Average Blk count Total
7/27/2006 9:50:56 AM:





7/27/2006 9:50:56 AM: Commit 888143872 4096 1185483 1719 2037846016
7/27/2006 9:50:56 AM: Reserve 4128768 4096 255454 248 63352832
7/27/2006 9:50:56 AM: Free 1572864 4096 101358 456 46219264
7/27/2006 9:50:56 AM: Private 888143872 4096 1743451 1177 2052042752
7/27/2006 9:50:56 AM: Mapped 1019904 4096 120496 55 6627328
7/27/2006 9:50:56 AM: Image 7569408 4096 57862 735 42528768
7/27/2006 9:50:56 AM:
7/27/2006 9:50:56 AM:
7/27/2006 9:50:56 AM: Warning 300: Backup failed. Retry attempt: 1
7/27/2006 9:50:58 AM: BACKUP DATABASE [dbAdmin] TO DISK = 'E:\SQLBACKUP\Database\dbAdmin\dbAdmin_FULL_20060727094944.sqb' WITH NAME = 'Database (dbAdmin), 7/27/2006 9:50:54 AM', DESCRIPTION = 'Backup on 7/27/2006 9:50:54 AM Server: PRODSQL91 Database: dbAdmin', INIT, COMPRESSION = 3

7/27/2006 9:50:59 AM: VDI error 1010: Failed to get configuration from server. Check that the SQL Server instance is running, and that you have the SQL Server Systems Administrator server role. Error code: (-2139684860: An abort request is preventing anything except termination actions.)
7/27/2006 9:51:00 AM: SQL error 3013: BACKUP DATABASE is terminating abnormally.
7/27/2006 9:51:00 AM:
7/27/2006 9:51:01 AM: Memory profile
7/27/2006 9:51:01 AM: Type Maximum Minimum Average Blk count Total
7/27/2006 9:51:01 AM:





7/27/2006 9:51:01 AM: Commit 888143872 4096 1186118 1718 2037751808
7/27/2006 9:51:01 AM: Reserve 4128768 4096 255454 248 63352832
7/27/2006 9:51:01 AM: Free 1572864 4096 101342 457 46313472
7/27/2006 9:51:01 AM: Private 888143872 4096 1746307 1175 2051911680
7/27/2006 9:51:01 AM: Mapped 1019904 4096 119003 56 6664192
7/27/2006 9:51:01 AM: Image 7569408 4096 57862 735 42528768
7/27/2006 9:51:01 AM:
7/27/2006 9:51:01 AM:
7/27/2006 9:51:01 AM: Warning 300: Backup failed. Retry attempt: 2
7/27/2006 9:51:03 AM: BACKUP DATABASE [dbAdmin] TO DISK = 'E:\SQLBACKUP\Database\dbAdmin\dbAdmin_FULL_20060727094944.sqb' WITH NAME = 'Database (dbAdmin), 7/27/2006 9:50:54 AM', DESCRIPTION = 'Backup on 7/27/2006 9:50:54 AM Server: PRODSQL91 Database: dbAdmin', INIT, COMPRESSION = 3

7/27/2006 9:51:03 AM: VDI error 1010: Failed to get configuration from server. Check that the SQL Server instance is running, and that you have the SQL Server Systems Administrator server role. Error code: (-2139684860: An abort request is preventing anything except termination actions.)
7/27/2006 9:51:04 AM: SQL error 3013: BACKUP DATABASE is terminating abnormally.
7/27/2006 9:51:04 AM:
7/27/2006 9:51:05 AM: Memory profile
7/27/2006 9:51:05 AM: Type Maximum Minimum Average Blk count Total
7/27/2006 9:51:05 AM:





7/27/2006 9:51:05 AM: Commit 888143872 4096 1185450 1719 2037788672
7/27/2006 9:51:05 AM: Reserve 4128768 4096 255454 248 63352832
7/27/2006 9:51:05 AM: Free 1572864 4096 101261 457 46276608
7/27/2006 9:51:05 AM: Private 888143872 4096 1746307 1175 2051911680
7/27/2006 9:51:05 AM: Mapped 1019904 4096 117562 57 6701056
7/27/2006 9:51:05 AM: Image 7569408 4096 57862 735 42528768
7/27/2006 9:51:05 AM:
7/27/2006 9:51:05 AM:
7/27/2006 9:51:05 AM: Warning 300: Backup failed. Retry attempt: 3
7/27/2006 9:51:07 AM: BACKUP DATABASE [dbAdmin] TO DISK = 'E:\SQLBACKUP\Database\dbAdmin\dbAdmin_FULL_20060727094944.sqb' WITH NAME = 'Database (dbAdmin), 7/27/2006 9:50:54 AM', DESCRIPTION = 'Backup on 7/27/2006 9:50:54 AM Server: PRODSQL91 Database: dbAdmin', INIT, COMPRESSION = 3

7/27/2006 9:52:13 AM: Backup data size : 765.188 MB
7/27/2006 9:52:13 AM: Compressed data size: 127.473 MB
7/27/2006 9:52:13 AM: Compression rate : 83.34%

Processed 97856 pages for database 'dbAdmin', file 'dbAdmin_Data' on file 1.
Processed 1 pages for database 'dbAdmin', file 'dbAdmin_Log' on file 1.
BACKUP DATABASE successfully processed 97857 pages in 63.160 seconds (12.692 MB/sec).



Here is the resulting output values:
name value
exitcode 300
sqlerrorcode 0
filename01 E:\SQLBACKUP\Database\dbAdmin\dbAdmin_FULL_20060727094944.sqb

Thank You,
Dan W

Comments

  • peteypetey Posts: 2,358 New member
    Exit codes 1 to 499 are warnings. That means that the backup file was created, but other post-backup operations may have failed e.g. copying the backup to another folder, failing to send e-mail etc.

    In the case of mirrored backups, as long as there was one backup file that was created successfully, only a warning is raised.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
Sign In or Register to comment.