I'm using Ola's Maintenance Solution to backup my databases to a URL. Most of my databases backup just fine but I have one 2 TB database that continues to fail with a device I/O error. Has anyone seen this behavior before using his scripts? I don't have a clue what I should do with this error.

  • 1
    Can you add the error message in the question, also I would tag question with azure – Shanky Jan 14 '19 at 16:23
  • 1
    What are you backing up to? An appliance? Does it support file sizes > 2TB? – Max Vernon Jan 14 '19 at 17:00
  • "continues to fail with a device I/O error." - find the error, open a report on that. Seriously, this is a low level error and you should investgate it. OTOH "some io error" is as unhelpfull as it gets. – TomTom Jan 14 '19 at 17:23
  • Please also provide the SQL Server version number. There was one issue that was fixed in a CU. support.microsoft.com/en-us/help/4463320/… – Ola Hallengren Jan 14 '19 at 21:45

I have seen a similar issue previously with large backups to Azure Blob Storage and the issue was hitting block blob limits. You might be hitting size limitations for the block blob size in Azure (link) depending on your version.

Try the following:

  • Enable compression to ensure your backup is as small as possible.
  • Stripe your backup over multiple files. To try and stay under the 195GB per blob limitation, you will need to use 12-15 striped files.
  • Set the BLOCKSIZE and MAXTRANSFERSIZE parameters in your backup command to 64 K and 4 MB respectively. This will help ensure maximum density of your blob blocks.

Alternatively, since SQL Server 2016 uses the older API, you can try backing up to a local disk then using the latest version of AzCopy to move the backup file up to Azure Storage. This will take longer but may help alleviate the device I/O error you're seeing.

Not the answer you're looking for? Browse other questions tagged or ask your own question.