Sql-server – Backup Failure on SQL TDE Database with Maxtransfersize Specified for Compression

backupcompressionsql serversql-server-2016transparent-data-encryption

Recently, my company has upgraded to 2016 and enabled TDE on all of our databases. Additionally, we have the production databases replicated on high availability servers.

Our backups are taken from the HA server and use the maxtransfersize hack in order to compress the backups.

All of the backups are successful, save one, where the following error is spit out by our monitoring software:

Error: 3624, Severity: 20, State: 1.

A system assertion check has failed. Check the SQL Server error log for details. Typically, an assertion failure is caused by a software bug or data corruption. To check for database corruption, consider running DBCC CHECKDB. If you agreed to send dumps to Microsoft during setup, a mini dump will be sent to Microsoft. An update might be available from Microsoft in the latest Service Pack or in a Hotfix from Technical Support.

In the server log there are a few more details at the point of failure:

input buffer 70 bytes

ProcessID: 1884

SPID:200

Expression: m_inputQueue.head()==null

location: backuplog.cpp 1564

This is my backup command:

backup database @DBName to disk = @Path 
  with noformat, noinit, name=@Name, skip, norewind, nounload, 
  compression, stats=10, copy_only, MAXTRANSFERSIZE = 65537

Has anyone run into a similar problem before?

I've used a few different values for maxtransfersize, albeit in a kinda random manner, picking from a chart in TDE and Backup Compression: Together At Last by Erik Darling.

Not using TDE is not an option because it contains sensitive data. Not being compressed may be an option if we have the drive space.

Best Answer

Community wiki answer

When I got back from lunch the backup completed successfully when maxtransfersize was not specified. After seeing it fail all morning, and now having one successful, uncompressed backup, it seems as if the failure is happening when it gets to the log file.

The error you're hitting is a sanity check (it seems) for having consumed all of the vlf which is why it errors there. I cannot tell you why it is failing the check. – Sean Gallardy Jan 3 2017

Thanks for the hint. The number of VLFs was the root of my problem. This particular database had CDC issues which meant the transaction log wasn't being cleared out on backups. It had grown to over 300 GB. Once I disabled CDC, I was able to flush the log and give it a better growth plan. The compressed backup works again.

Related Question