Compressed backup errors and TF 3042

6

March 16, 2017 by Kenneth Fisher

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

6 thoughts on “Compressed backup errors and TF 3042

  1. londondba says:

    It’s a pity this is a trace flag and not a backup database option. For example, your sql server has twenty databases of which you you only want to apply this to one large database. Either you set a global trace flag and it applies to every backup of you modify your backup script to set the session trace flag just before you run the backup of the one large database. Far better if this had been a backup database option,

  2. Nice one, didn’t know of this one.

  3. Armando Lacerda says:

    Valuable info. Good to know. Thanks.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 1,627 other followers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: