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

• 
  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.

  petey August 05, 2011 20:50

  - 0 +
• 
  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.

  jlynnmc10 August 08, 2011 14:23

  - 0 +
• 
  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?

  jlynnmc10 August 08, 2011 19:12

  - 0 +
• 
  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.

  petey August 09, 2011 00:29

  - 0 +
• 
  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.

  jeff.stanlick August 12, 2011 10:35

  - 0 +
• 
  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.

  petey August 12, 2011 10:59

  - 0 +
• 
  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

  jlynnmc10 August 23, 2011 19:20

  - 0 +
• 
  131072 is 20000 in hex.

  petey August 23, 2011 19:55

  - 0 +
• 
  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.

  jlynnmc10 August 23, 2011 20:41

  - 0 +
• 
  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.

  petey August 23, 2011 21:40

  - 0 +

Add comment

Please sign in to leave a comment.