Last night my weekly backup on SQL Server 2014, running on Windows Server 2012 R2, failed with the following error in the log:

failed with the following error: "A nonrecoverable I/O error occurred on file "D:\MSSQL\MSSQL12.MSSQLSERVER\Backup\backup-xxxx.bak:" 112(There is not enough space on the disk.).

When I checked disk space I found there was still 1 GB free, and the .bak file looked ok, all 4GB of it, not compressed.

So I ran a RESTORE VERIFYONLY command on that file:

RESTORE VERIFYONLY FROM DISK = 'D:\MSSQL\MSSQL12.MSSQLSERVER\Backup\backup_xxxx.bak'

that responded:

The backup set on file 1 is valid.

So which version of the story should I trust?

Note: According to Pinal Dave (http://blog.sqlauthority.com/2016/04/14/sql-server-backup-randomly-failing-error-112-not-enough-space-disk/) the first error is not accurate, especially if using compressed backups.

  • Are you compressing the backup or not though? – Molenpad Jun 6 '16 at 10:50
  • 1
    Run RESTORE HEADERONLY ... to see what date the backup was taken. Perhaps the backup is actually from the previous week. – Max Vernon Jun 6 '16 at 10:52
  • 2
    Also if you didn't back up with INIT, the backup operation that caused the error might not have been the backup stored as file 1. SQL Server doesn't raise out of space issues for fun and games, it only does that when it actually comes across a space problem, in spite of whatever Pinal might say about it. – Aaron Bertrand Jun 6 '16 at 10:53
  • What Pinal is saying might be related to your scenario if you are using compression. Yes the way compression works for allocating space is bit different. But the crux is during backup SQL Server is not able to find enough space and so it is failing. – Shanky Jun 6 '16 at 11:21
  • @Molenpad: The backup is not compressed. – simonpa71 Jun 6 '16 at 12:46

Ok, my bad. :-/ SQL Server is correct on both accounts.

The weekly full backup starts at 6 PM on Sundays, and the daily incremental backup starts at 8 PM. The weekly backup is still running when the daily incremental starts. The daily incremental records a .bak file that is the same size as the full, because ?? all indexes, statistics and so on have changed.

At this point, or rather, some two hours later, when it has finished reorganizing indexes and updating statistics, there isn't enough disk left for the full backup.

The incremental backup that I checked with RESTORE VERIFYONLY is ok. The full backup was never created.

So now I have to choose between some options:

  • start the Full backup a few hours earlier,
  • skip the incremental on Sundays,
  • (suggestions welcome ... )
  • 1
    Are you backing up your database to the same disk that hosts your data and/or log files for SQL Server? Let me count the ways this is a serious problem... – Aaron Bertrand Jun 6 '16 at 13:42
  • @Aaron: well spotted. This is the configuration I inherited, and I am learning the hard way... – simonpa71 Jun 10 '16 at 12:43

Your Answer

 
discard

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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