Backup script is ignoring maxtransfersize

MeebleMeeble Posts: 3
edited September 29, 2008 10:37PM in SQL Backup Previous Versions
This is the script I recently switched over to since this particular script has been failing a lot recently. Every time it errors out, it always retrys with the smaller maxtransfersize and succeeds. I want to make only this one have the smaller tranfers size, and so far its ignoring what i have down below. Am I missing something?
I have version 5.3.0.178

exec master..sqlbackup N'-SQL "BACKUP LOG [GMail] TO DISK = ''<AUTO>'' WITH NAME = ''<AUTO>'', DESCRIPTION = ''<AUTO>'', INIT, COMPRESSION = 2, MAXTRANSFERSIZE = 131072, COPYTO = ''\\~~~\E$\Backup\FBSQL102\GlobalMail'', MAXTRANSFERSIZE = 262144"', @exitcode OUTPUT, @sqlerrorcode OUTPUT
SQL Backup log file 5.3.0.178

-SQL "BACKUP LOG &#91;Gmail&#93; TO DISK = '&lt;AUTO&gt;' WITH NAME = '&lt;AUTO&gt;', DESCRIPTION = '&lt;AUTO&gt;', INIT, COMPRESSION = 2, MAXTRANSFERSIZE = 131072, COPYTO = '\\~~~\E$\Backup\FBSQL102\Gmail', MAXTRANSFERSIZE = 262144 "

-----------------------------  ERRORS AND WARNINGS -----------------------------


9/25/2008 8:00:00 AM: Backing up Gmail &#40;transaction log&#41; to: 
9/25/2008 8:00:00 AM:   K:\Backup\FBSQL102\Gmail\Gmail_LOG_20080925_080000.sqb

9/25/2008 8:00:01 AM: BACKUP LOG &#91;Gmail&#93;  TO VIRTUAL_DEVICE = 'SQLBACKUP_B42A5D5D-981D-4F4A-8152-2B9178D3BF4A' WITH BUFFERCOUNT = 6, BLOCKSIZE = 65536, MAXTRANSFERSIZE = 262144, NAME = N'Database &#40;Gmail&#41;, 9/25/2008 8:00:00 AM', DESCRIPTION = N'Backup on 9/25/2008 8:00:00 AM  Server: FBSQL102  Database: Gmail'

9/25/2008 8:00:31 AM: VDI error 1010: Failed to get the configuration from the server because the timeout interval has elapsed. Check that the SQL Server instance is running and that you have the SQL Server System Administrator server role; or try increasing the value of the VDITimeout registry setting in HKEY_LOCAL_MACHINE\SOFTWARE\Red Gate\SQL Backup\BackupSettingsGlobal\&#91;InstanceName&#93;
9/25/2008 8:01:11 AM: SQL error 3013: BACKUP LOG is terminating abnormally.
9/25/2008 8:01:11 AM: SQL error 3201: Cannot open backup device 'SQLBACKUP_B42A5D5D-981D-4F4A-8152-2B9178D3BF4A'. Operating system error 0x80070002&#40;The system cannot find the file specified.&#41;.
9/25/2008 8:01:11 AM: 
9/25/2008 8:01:11 AM: Memory profile
9/25/2008 8:01:11 AM: Type             Maximum     Minimum     Average     Blk count   Total      
9/25/2008 8:01:11 AM: ---------------- ----------- ----------- ----------- ----------- -----------
9/25/2008 8:01:11 AM: Commit           15549333504 4096        9404433     2965        27884146688
9/25/2008 8:01:11 AM: Reserve          7734231040  4096        15382733    509         7829811200 
9/25/2008 8:01:11 AM: Free             6846142611456 4096        21109346985 415         8760378998784
9/25/2008 8:01:11 AM: Private          15549333504 4096        12251872    2903        35567185920
9/25/2008 8:01:11 AM: Mapped           4190208     4096        198144      64          12681216   
9/25/2008 8:01:11 AM: Image            33226752    4096        264478      507         134090752  
9/25/2008 8:01:11 AM: 
9/25/2008 8:04:26 AM: Copied K:\Backup\FBSQL102\Gmail\Gmail_LOG_20080925_080000.sqb to \\~~~\E$\Backup\FBSQL102\Gmail\Gmail_LOG_20080925_080000.sqb.



-----------------------  PROCESSES COMPLETED SUCCESSFULLY   --------------------

9/25/2008 8:01:11 AM: Warning 300: Backup failed.  Retry attempt: 1
9/25/2008 8:01:14 AM: BACKUP LOG &#91;Gmail&#93;  TO VIRTUAL_DEVICE = 'SQLBACKUP_863BED06-E69A-4003-9414-353C08277401' WITH BUFFERCOUNT = 6, BLOCKSIZE = 65536, MAXTRANSFERSIZE = 131072, NAME = N'Database &#40;Gmail&#41;, 9/25/2008 8:00:00 AM', DESCRIPTION = N'Backup on 9/25/2008 8:00:00 AM  Server: FBSQL102  Database: Gmail'

9/25/2008 8:03:38 AM: Backup data size    : 273.875 MB
9/25/2008 8:03:38 AM: Compressed data size: 69.674 MB
9/25/2008 8:03:38 AM: Compression rate    : 74.56%

Processed 34784 pages for database 'Gmail', file 'Gmail_log' on file 1.
BACKUP LOG successfully processed 34784 pages in 7.523 seconds &#40;37.876 MB/sec&#41;.

Comments

  • peteypetey Posts: 2,358 New member
    You have another 'MAXTRANSFERSIZE = 262144' option in your command. SQL Backup used the later value. Remove that option and the max. transfer size should use 131072 bytes.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • So now it seems that its still failing on us, but picking an even smaller size to complete. I know this server is being hit hard, but is there anyway to make it so it doesn't notify us that the job has failed even though the backup succeeded?
  • peteypetey Posts: 2,358 New member
    When a backup succeeds only after subsequent reattempts, a warning code (300) is raised. This is subsequently returned as the exit code. All SQL Backup exit codes less than 500 are warnings, meaning that the backup ultimately succeeded, but one or more processes may have encountered non-critical issues along the way.

    Depending on your notification mechanism, you could choose to ignore SQL Backup exit codes with a value less than 500.

    Note that the SQL error code is an entirely different thing altogether. This is the last SQL Server error code returned by SQL Server, and is usually critical if it is a value other than 0.
    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.