Backup only succeeds after retry

philcartphilcart Posts: 45
edited October 29, 2007 4:25PM in SQL Backup Previous Versions
Using SQL Backup 4.6.0.815

Following is the SQL Backup log file
8/05/2007 4:31:40 PM: Backing up mydb (full database) to: 
  E:\Backups\FULL_mydb_20070508163140_01.sqb
  E:\Backups\FULL_mydb_20070508163140_02.sqb

8/05/2007 4:31:40 PM: BACKUP DATABASE [mydb]  TO DISK = 'E:\Backups\FULL_mydb_20070508163140_01.sqb', DISK = 'E:\Backups\FULL_mydb_20070508163140_02.sqb' WITH NAME = '<AUTO>', DESCRIPTION = '<AUTO>', ERASEFILES = 1, MAILTO = 'me@somewhere.com', COMPRESSION = 2, THREADS = 2

8/05/2007 4:31:56 PM: 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:  (-2139684861: The api was waiting and the timeout interval had elapsed.)
8/05/2007 4:44:58 PM: SQL error 3013: BACKUP DATABASE is terminating abnormally.
8/05/2007 4:44:58 PM: SQL error 3201: Cannot open backup device 'SQLBACKUP_5A6E66BB-D633-4108-B649-A9CA4D6C4ADD'. Operating system error 0x80070002(error not found).
8/05/2007 4:44:58 PM: 
8/05/2007 4:44:58 PM: Memory profile
8/05/2007 4:44:58 PM: Type             Maximum     Minimum     Average     Blk count   Total      
8/05/2007 4:44:58 PM: ---------------- ----------- ----------- ----------- ----------- -----------
8/05/2007 4:44:58 PM: Commit           100450304   4096        969651      1838        1782218752 
8/05/2007 4:44:58 PM: Reserve          32378880    4096        159798      522         83415040   
8/05/2007 4:44:58 PM: Free             114884608   4096        1523158     185         281784320  
8/05/2007 4:44:58 PM: Private          100450304   4096        947806      1887        1788510208 
8/05/2007 4:44:58 PM: Mapped           1060864     4096        109391      58          6344704    
8/05/2007 4:44:58 PM: Image            24858624    4096        170551      415         70778880   
8/05/2007 4:44:58 PM: 
8/05/2007 4:44:58 PM: Warning 300: Backup failed.  Retry attempt: 1
8/05/2007 4:45:00 PM: BACKUP DATABASE [mydb]  TO DISK = 'E:\Backups\FULL_mydb_20070508163140_01.sqb', DISK = 'E:\Backups\FULL_mydb_20070508163140_02.sqb' WITH NAME = 'Database (mydb), 8/05/2007 4:31:40 PM', DESCRIPTION = 'Backup on 8/05/2007 4:31:40 PM  Server: SQLSERV  Database: mydb', INIT, ERASEFILES = 1, MAILTO = 'me@somewhere.com', COMPRESSION = 2, THREADS = 2

8/05/2007 5:11:19 PM: Backup data size    : 87.236 GB
8/05/2007 5:11:19 PM: Compressed data size: 30.432 GB
8/05/2007 5:11:19 PM: Compression rate    : 65.11%

Processed 11425424 pages for database 'mydb', file 'mydb_Data' on file 1.
Processed 6479 pages for database 'mydb', file 'mydb_Log' on file 1.
BACKUP DATABASE successfully processed 11431903 pages in 1572.999 seconds (59.536 MB/sec).

Is this related to the contiguous free memory issue that's resolved with the MAXTRANSFERSIZE option?

If so, does SQL Backup v5 show the revised backup command with the adjusted MAXTRANSFERSIZE value?

Thanks
Phill
· Share on Twitter

Comments

  • Chris AucklandChris Auckland Posts: 769
    Yes, this issue should be resolved by specifying a MAXTRANSFERSIZE parameter in the backup job.

    With v5 you are now able to specify the MAXTRANSFERSIZE parameter while setting up a job. You can choose from the largest of 1024kb right down to 64kb, so it should be easy to fix if you ever get these warnings again.
    Chris
    · Share on Twitter
  • sidownessidownes Posts: 6
    I'm experiencing the same problem, also running SQL Backup 4.6.0.815. I have already specified a value for MAXTRANSFERSIZE of 262144 but the problem persists
    20/09/2007 20:00:05: BACKUP LOG [Aries_Test]  TO DISK = 'E:\Aries_Test\LOG_(local)_Aries_Test_20070920_200005.sqb' WITH NAME = '<AUTO>', DESCRIPTION = '<AUTO>', ERASEFILES_ATSTART = 7, MAILTO_ONERROR = 'dbu@bl.uk', COMPRESSION = 3, THREADS = 1

20/09/2007 20:00:05: Deleting old backup file: E:\Aries_Test\LOG_(local)_Aries_Test_20070912_200006.sqb
20/09/2007 20:00:20: 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:  (-2139684861: The api was waiting and the timeout interval had elapsed.)
20/09/2007 21:26:42: SQL error 3013: BACKUP LOG is terminating abnormally.
20/09/2007 21:26:42: 
20/09/2007 21:26:43: Memory profile
20/09/2007 21:26:43: Type             Maximum     Minimum     Average     Blk count   Total      
20/09/2007 21:26:43: ---------------- ----------- ----------- ----------- ----------- -----------
20/09/2007 21:26:43: Commit           840499200   4096        1356706     1276        1731158016 
20/09/2007 21:26:43: Reserve          27328512    4096        801007      197         157798400  
20/09/2007 21:26:43: Free             163753984   4096        1353202     191         258461696  
20/09/2007 21:26:43: Private          840499200   4096        2034763     898         1827217408 
20/09/2007 21:26:43: Mapped           1536000     4096        178211      57          10158080   
20/09/2007 21:26:43: Image            7569408     4096        99577       518         51580928   
20/09/2007 21:26:43: 
20/09/2007 21:26:43: Warning 300: Backup failed.  Retry attempt: 1
20/09/2007 21:26:45: BACKUP LOG [Aries_Test]  TO DISK = 'E:\Aries_Test\LOG_(local)_Aries_Test_20070920_200005.sqb' WITH NAME = 'Database (Aries_Test), 20/09/2007 20:00:05', DESCRIPTION = 'Backup on 20/09/2007 20:00:05  Server: [myServer] Database: Aries_Test', INIT, ERASEFILES_ATSTART = 7, COMPRESSION = 3, THREADS = 1

20/09/2007 21:26:45: Deleting old backup file: E:\Aries_Test\LOG_(local)_Aries_Test_20070913_200006.sqb
20/09/2007 21:26:45: Backup data size    : 640.000 KB
20/09/2007 21:26:45: Compressed data size: 7.000 KB
20/09/2007 21:26:45: Compression rate    : 98.91%

Processed 1 pages for database 'Aries_Test', file 'TPM_Log' on file 1.
BACKUP LOG successfully processed 1 pages in 0.005 seconds (0.512 MB/sec).
--------------------------------------------------------------------------------
    · Share on Twitter
  • Chris AucklandChris Auckland Posts: 769
    In your case it looks like it succeeded on the second attempt. You may find that if the step down the MAXTRANSFERSIZE parameter to 131072 you will no longer receive the warnings.

    I hope this helps.
    Chris
    · Share on Twitter
  • sidownessidownes Posts: 6
    No good, still same problem of it only working intermittently on first attempt. Parameters set to MAXDATABLOCK = 131072 and
    MAXTRANSFERSIZE = 65536
    · Share on Twitter
  • sidownessidownes Posts: 6
    bump
    · Share on Twitter
  • Chris AucklandChris Auckland Posts: 769
    Sorry about this delay. I must have missed your last update.

    Here is a link to a knowledgebase article that lists some common solutions to this issue.
    http://www.red-gate.com/support/kb/KB200710000177.htm

    If the server has also been running for a long time, and under somewhat heavy load, I think it would be a good idea to restart the SQL Server service if possible. This shouldn't really be necessary with modern versions of SQL Server, but it has been known to resolve these issues in the past.

    Have you tried SQL Backup 5? There have been some improvements to the way that the SQL backup task is retried if the SQL Server is low on contiguous memory. This may also fix the problem for you.
    Chris
    · Share on Twitter
Sign In or Register to comment.