0

I've two SQL servers with multiple DBs. On both, I've set up maintenance tasks using the Ola procedures. One DB server is Enterprise SQL with Agent and the other is Express using Win scheduler to run procedures.

All database backups are done towards a network share (QNAP NAS) with plenty of space (800Gb with 750GB still free). ALL of the backups, of system DBS and user DBS works perfectly but, a differential backup of the main user DB is always failing with I/O error,

Message 3271, gravity 16, error 112 Not enough space on disk

which obviously is a false error reason. I'm running FULL backups at 6:00 and 20:00 with DIFF backups at lunch break (13:00). In between LOG backups every hour. Please note that ALL of the other backups run, even the DIFF backups on smaller user DBs, the only one failing is always on the same DB (which is the biggest, most important and active).

  • All checks on DB give no errors.
  • Networks shares works.
  • Surfing forum I noticed that others are experiencing such I/O error erratically.
  • SQL Server version is 13.0.5101.9

Any ideas?

  • 1
    What's the database size? Can you create the DIFF backup of that database runnig the backup command separately or it gives the same error you're getting from the job? Are you splitting the backup? Microsoft has A Case Study: Fast and Reliable Backup and Restore of a VLDB over the Network, have you seen it? – Ronaldo Jan 17 at 14:57
  • As suggested by Ronaldo, I would try to run the diff backup in TSQL. If it works, I would check the job definition (specially the path where the backup is supposed to be saved). – Dominique Boucher Jan 17 at 15:37
  • Hello thank for comments. Database size is 4.3Gb but the FULL backups are running smoothly. I've tried to run the differential backup to the same network share path through the Mgmt Studio interface and it runs with no problems in few seconds. – maurpra Jan 21 at 14:14
1

For the help of everybody I post the resolution I did found to my problem. Scanning other forums I noticed that this I/O error 112 - "not enough space on disk" is fairly a common issue, erratic but not so infrequent.

I read a post where the issue is described to come from the sql backup algorithm which, before starting the backup operation itself, is calculating, based on DB dimension, how much space on disk will be needed. If the result (space calculated to be needed) is not enough, you get the error and the backup is aborted. Unfortunately sometime this algorithm seems to have glitches. This should be applicable on backups made with compression option on only, but in my (and other) cases may create issues independently from.

There is a TRACE OPTION which is disabling such algorithm, and the backups start anyhow without such pre-check. I've therefore set DBCC TRACEON(3042, -1) on the SQL instance, as suggested, and bingo!, now also my DIFF backups are going on smoothly.

Thanks anyhow for the interest.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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