Backup fails with "Insufficient system resources exist ...."

jlynnmc10jlynnmc10 Posts: 10
edited August 23, 2011 9:40PM in SQL Backup Previous Versions
SQL Server 2005 SP3 Enterprise Edition
Windows Server 2003 Enterprise Edition
SQL Backup Version: 6.5.1.9

All backups (Full, Differential and Incremental) fail with the below errors. I am able to successfully backup to the exact same network share using SQL native backups. Backups only fail using RedGate SQL Backup.

Thread 0 error:
Error 620: Error writing to backup file(s).
Process terminated unexpectedly. Error code: -2139684860 (An abort request is preventing anything except termination actions.)
Warning 210: Thread 0 warning:
WriteFile failed for file: \\hooka\connected_backup_india\INDIACON01\Directory\Directory_SB_20110726223004.BAK at position: 1024
7/26/2011 10:30:06 PM: WriteFile failed for file: \\hooka\connected_backup_india\INDIACON01\Directory\Directory_SB_20110726223004.BAK (Insufficient system resources exist to complete the requested service.)
7/26/2011 10:30:36 PM: Re-attempt: 1
7/26/2011 10:30:36 PM: WriteFile failed for file: \\hooka\connected_backup_india\INDIACON01\Directory\Directory_SB_20110726223004.BAK (Insufficient system resources exist to complete the requested service.)
7/26/2011 10:31:06 PM: Re-attempt: 2
7/26/2011 10:31:06 PM: WriteFile failed for file: \\hooka\connected_backup_india\INDIACON01\Directory\Directory_SB_20110726223004.BAK (Insufficient system resources exist to complete the requested service.)
7/26/2011 10:31:36 PM: Re-attempt: 3
7/26/2011 10:31:36 PM: WriteFile failed for file: \\hooka\connected_backup_india\INDIACON01\Directory\Directory_SB_20110726223004.BAK (Insufficient system resources exist to complete the requested service.)
7/26/2011 10:32:06 PM: Re-attempt: 4
7/26/2011 10:32:06 PM: WriteFile failed for file: \\hooka\connected_backup_india\INDIACON01\Directory\Directory_SB_20110726223004.BAK (Insufficient system resources exist to complete the requested service.)
7/26/2011 10:32:36 PM: Re-attempt: 5
7/26/2011 10:32:36 PM: WriteFile failed for file: \\hooka\connected_backup_india\INDIACON01\Directory\Directory_SB_20110726223004.BAK (Insufficient system resources exist to complete the requested service.)
7/26/2011 10:33:06 PM: Re-attempt: 6
7/26/2011 10:33:06 PM: WriteFile failed for file: \\hooka\connected_backup_india\INDIACON01\Directory\Directory_SB_20110726223004.BAK (Insufficient system resources exist to complete the requested service.)
7/26/2011 10:33:36 PM: Re-attempt: 7
7/26/2011 10:33:37 PM: WriteFile failed for file: \\hooka\connected_backup_india\INDIACON01\Directory\Directory_SB_20110726223004.BAK (Insufficient system resources exist to complete the requested service.)
7/26/2011 10:34:07 PM: Re-attempt: 8
7/26/2011 10:34:07 PM: WriteFile failed for file: \\hooka\connected_backup_india\INDIACON01\Directory\Directory_SB_20110726223004.BAK (Insufficient system resources exist to complete the requested service.)
7/26/2011 10:34:37 PM: Re-attempt: 9
7/26/2011 10:34:37 PM: WriteFile failed for file: \\hooka\connected_backup_india\INDIACON01\Directory\Directory_SB_20110726223004.BAK (Insufficient system resources exist to complete the requested service.)
7/26/2011 10:35:07 PM: Re-attempt: 10
7/26/2011 10:35:07 PM: WriteFile failed for file: \\hooka\connected_backup_india\INDIACON01\Directory\Directory_SB_20110726223004.BAK (Insufficient system resources exist to complete the requested service.)

SQL error 3013: SQL error 3013: BACKUP DATABASE is terminating abnormally.
SQL error 3271: SQL error 3271: A nonrecoverable I/O error occurred on file "SQLBACKUP_45011AFF-8875-42F3-84F4-D90AE84F3954:" 995(The I/O operation has been aborted because of either a thread exit or an application request.).
SQL error 3202: SQL error 3202: Write on "SQLBACKUP_45011AFF-8875-42F3-84F4-D90AE84F3954" failed: 1117(The request could not be performed because of an I/O device error.)

SQL Backup exit code: 790
SQL error code: 3202
· Share on Twitter

Comments

  • peteypetey Posts: 2,358 New member
    Could you please try running the same backup, using a MAXDATABLOCK value of 131072 e.g.

    EXEC master..sqlbackup '-sql "BACKUP ... WITH MAXDATABLOCK = 131072"'

    Thanks.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
    · Share on Twitter
  • jlynnmc10jlynnmc10 Posts: 10
    The backup was successful when changing the MAXDATABLOCK value to 131072. Can I change this to be the default value for the server or can MAXDATABLOCK only be specified when executing the BACKUP command?

    Thank you very much for your help.
    · Share on Twitter
  • jlynnmc10jlynnmc10 Posts: 10
    I also found that the following registry setting does not exist:
    HKEY_LOCAL_MACHINE\SOFTWARE\Red Gate\SQL Backup\BackupSettingsGlobal\<instance name>\MAXDATABLOCK

    If this setting does not exist then what is the default value? Can I add this setting and set it to 131072?
    · Share on Twitter
  • peteypetey Posts: 2,358 New member
    I think you should first understand what MAXDATABLOCK does.

    When SQL Backup writes out compressed backup data to disk, it does so in blocks of 1 MB. In your case, it looks like somewhere along your network path, writing 1 MB in one go is impossible. Writing the file out in smaller blocks of 128 Kb seems to be fine. Do you know why this is so? Does this restriction apply even if you back up to other network shares?

    Writing many smaller blocks is slower than writing a single large block, hence your backup speed would be slower with a smaller MAXDATABLOCK value. How much slower would depend on your systems configuration.

    Also, note that 128 Kb is just a test value I wanted you to try. It is small enough that it usually succeeds. You can try any value up to 1048576 (1 MB), as long as they are in increments of 64 Kb. So you could try 262144 (256 Kb) or 524288 (512 Kb).

    If you don't explicitly use the MAXDATABLOCK option, the default value is 1048576 (1 MB). If you want to apply a different default value, create a 'MaxDataBlock' value in the SQL Backup registry node, of type DWORD. Enter your value there. This becomes the default value SQL Backup will use when you do not explicitly provide a MAXDATABLOCK value in your backup commands.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
    · Share on Twitter
  • jeff.stanlickjeff.stanlick Posts: 7
    This thread was timed perfectly as I just ran into the same issue and the suggestion helped me out. However, I have a comment related to the UI and the default value for MAXDATABLOCK.

    The UI shows a maximum value of 2048 KB and it's the MAXTRANSFERSIZE that has a max of 1024 KB. Looking at the defaults on the screen, I thought the parameter may have been mislabeled in the thread so I changed the MAXTRANSFERSIZE to 512 KB then to 128 KB (I figured I'd work my way down until it worked to get the most speed available) and got the same errors. I changed the MAXDATABLOCK to 1024 KB and my backups started running again. So, I think the parameter name is correct, but perhaps the values are mis-stated? Or am I understanding this incorrectly? I'm also running version 6.5.1.9.
    · Share on Twitter
  • peteypetey Posts: 2,358 New member
    Jeff, you are right, the default value for MAXDATABLOCK is indeed 2 MB, and not 1 MB as I previously stated. Thank you for pointing that out.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
    · Share on Twitter
  • jlynnmc10jlynnmc10 Posts: 10
    My backups write succesfully to the network share if I use native SQL backups but I want to use RedGate for the compression so I am still trying to trouble shoot this issue.

    If I run the backup using the RedGate UI or run EXEC master..sqlbackup and specify a MAXDATABLOCK of 131072 my backups are successful and do not take too long so I would like to implement this setting for all backups on this one server.

    I created a new registry key in the following location: HKEY_LOCAL_MACHINE\SOFTWARE\Red Gate\SQL Backup\BackupSettingsGlobal\(LOCAL)

    With these values:
    Type = DWORD
    Value name = MAXDATABLOCK
    Value data = 131072
    Hexadecimal

    But when I run EXEC master..sqlbackup without specifying maxdatablock my backup still fails. Do I need to restart something or do something else for this registry setting to take effect? Shouldn't I be able to execu sqlbackup without specifying MAXDATABLOCK and it read the value from the registry?

    Thanks. Jeralynn
    · Share on Twitter
  • peteypetey Posts: 2,358 New member
    131072 is 20000 in hex.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
    · Share on Twitter
  • jlynnmc10jlynnmc10 Posts: 10
    What is 262144 in hex?

    Thank you so much for your help!! My backups are now successful which helps me feel better why the Windows and Network teams continue to do their research.
    · Share on Twitter
  • peteypetey Posts: 2,358 New member
    262144 is 40000 in hex. You can use the windows calculator to perform the conversions, or enter the values in decimal instead in the registry.
    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.