Solving puzzle is something everyone would love to do. It gets our creative thoughts and the things that it challenges how we can solve problems. Recently I bought a puzzle which my kid had to finish in her school holidays. I was amazed to how complex it was. I saw that my kid and wife had taken almost a week to solve it. But once they finished, the expression in their faces to show-off was amazing. I felt it was worth every penny I had spent on it. Few people like detective series because of the same reason. And I also got a similar chance while working with a client in the recent past. Here is the puzzle which they wanted me to solve for Error 112.
As per my client, the problem started a few weeks ago. On random basis the backup of the database was failing with below error in maintenance plan history.
Executing the query “BACKUP DATABASE [PRODDB] TO DISK = N’\\…” failed with the following error: “The operating system returned the error ‘112(There is not enough space on the disk.)’ while attempting ‘SetEndOfFile’ on ‘\\\\fs01\\_SQL_Backups\\proddb_backup_2016_04_06_231005_3910233.bak’.
BACKUP DATABASE is terminating abnormally.
As we can see, the backups were taken at network location. They took a backup locally and it was lesser than free space available so backup would have succeeded. I have asked if there has any special setting done for backup. All they told me that they are using compressed backup.
Resolution for Error 112
I did some research and found that the backup size of compressed backup would be different in the beginning and at the end. As we can think, the size completely depends how compressing the data is and SQL Server cannot determine that beforehand. Based on my research, compressed backup completely works on the pre allocate, it may succeed if disk space is less and may fail if disk space is more depending on the pre-allocation file size and final compressed backup size.
I have asked to enable the trace flag 3042. By enabling this trace flag, backup bypasses the “pre-allocation algorithm”. This means it will have the most optimized space beforehand.
Here is the code to enable the traceflag 3042.
DBCC TRACEON (3042,-1); GO
If due to any reason, you want to turn of the traceflag, you can use following code.
DBCC TRACEOFF (3042,-1); GO
As a general rule of thumb, I have asked my client to have at least 1/3 of the volume of backup. So in this situation, the size of backup database is 50 GB then 1/3 should be at least 17 GB. I have asked them to make sure that they have that much space available in the backup drive. Again, this is not an algorithm, it is general guidance. True space can only be determined after the compressed backup is completed.
After enabling the trace flag 3042, SQL Server really grew the backup file bit by bit.
You can read more about this feature in my blog below.
- Introduction to New Feature of Backup Compression
- Data and Page Compressions – Data Storage and IO Improvement
- Effect of Compressed Backup Setting at Server Level on Database Backup
Reference: Pinal Dave (https://blog.sqlauthority.com)