Compressing your backups has very few downsides. It’s usually faster (the additional time for compression is less than the time saved by reduced IO) and of course, the backups are smaller. I have run into a few issues, however.

On one occasion there was 75gb free on a drive, the last full backup was only 50gb and the database had not grown significantly in size. Interestingly there was an error when we tried to run a backup. Not enough disk space.

On another occasion (and boy this was a pain) the backup would succeed but would still return the following error. There are several versions of the error so this is approximate.

“The operating system returned the error ?????

while attempting ‘SetEndOfFile’ on ‘\\SQLBackups\database.bak’.

BACKUP DATABASE is terminating abnormally.

Both situations actually turned out to have the same source. When a compressed backup starts SQL runs a compression pre-allocation algorithm to calculate how much space it will need. SQL then requests that much space from the OS. Then if the calculation is wrong it adjusts. If it needs more, SQL will request more. If on the other hand, it needs less, then at the end of the backup SQL will return that space back to the OS. This is faster than requesting the space in small increments and avoids the risk of there not being enough space.

In the case of both of the above errors, the calculation was off. In both cases the calculation caused SQL to request more space than it was going to need. In the first case, this was more space than was available, causing an insufficient disk space error.

In the second case there was plenty of space, and when the backup was complete SQL told the OS it could take back the extra space. Unfortunately, sometimes that step can be time-consuming (don’t ask me why). In our case, this caused a timeout and the job failed. Of course, the backup file had already been written, and (eventually) the excess space was released back.

So what is the fix? Trace flag 3042 changes the above behaviour. Per the trace flag listing:

Bypasses the default backup compression pre-allocation algorithm to allow the backup file to grow only as needed to reach its final size. This trace flag is useful if you need to save on space by allocating only the actual size required for the compressed backup. Using this trace flag might cause a slight performance penalty (a possible increase in the duration of the backup operation). For more information about the pre-allocation algorithm, see Backup Compression (SQL Server).

So rather than asking for the expected amount it starts small and grows a bit at a time. This takes longer but avoids the problems I mentioned above.

Now it does take longer. So this is a solution to a problem, not something you should use in the absence of a real problem.

Further reading: Testing SQL Server Backup Performance with Trace Flag 3042

Filed under: Backups, Microsoft SQL Server, Problem Resolution, SQLServerPedia Syndication Tagged: backups, microsoft sql server, problem resolution