Database Full Backup Maintenance Task has large delay between databases

Question

I have several database servers that use the same basic maintenance task to do daily full backups over night. Intermittently the backup job which normally takes 4 or 5 hours will suddenly take 13 to 15 hours. At first I figured the network was busy or the operations team started their nightly processing early, but I've been digging into this further and I noticed that something odd.

The maintenance task is set to do a backup of all databases ignoring those that are offline. It saves the backups to a Data Domain server. This is what is expected to happen and usually does:

• At 9pm the backup job starts.
• System databases backup first and take a combined 2 minutes
• DB1 backup starts and takes 1.5-2.5 hours done around 11pm give or take
• DB2 backup starts and takes 2.5-3.5 hours
• Job finishes between 1-3 am

Looking at the backup set history in MSDB this is what I'm seeing

• At 9pm the backup job starts.
• System databases backup first and take a combined 2 minutes
• DB1 backup starts and takes 1.5-2.5 hours done around 11pm give or take
• tsql command to start backup of DB2 begins executing
• 7-9 hour delay
• DB2 backup actually started, according to MSDB.dbo.backupset history, and takes 2.5-3.5 hours
• Job finishes between 8am and 12pm

As far as I can tell there is no activity before 2am that should be causing locks or contention, and the delays appear to be happening irregularly

My Question is this, what could be causing this large delay from when tsql is executed to when the backup is actually started?

Edit Rough Server Specs: VM Ware Virtual Machine (All resources dedicated) Windows Server2012 R2 SQL Server 2014 SP2 CPU: 16 total cores RAM: 225GB

Answer 1

Are you doing native SQL backups to a normal CIFS share on the Data Domain appliance?

Or are you using the "DDBoost" agent to back up directly to an MTREE?

If the latter, we had similar issues (although with tran log backups, not fulls) with the DDBoost v2.0 agent, which went away after upgrading to v3.5 (the latest).

A couple of other ideas to rule out:

1. Make sure you aren't doing a "verify backup", which just makes sure it can re-read the entire backup file back from disk (it is of little use, and takes just as long as the original backup). See this related question.
2. Looks like your box should have plenty of memory, but make sure the OS isn't being starved for RAM. 3rd party backup agents and SSIS packages run outside the SQL memory space, so check your max memory settings, and check task manager or the memory counters in perfmon.

Answer 2

This is sometimes due to an MSDB.dbo.backupsethistory table that's never been purged. SQL Server inserts a row into that table, then goes back and updates it later during the course of doing backups.

If your MSDB is on really slow storage, and you can't cache it in memory, and it's got a lot of history, then accessing that table can be your biggest bottleneck. I wrote a blog post called Brent's Backup Bottleneck: MSDB about a really bad occurrence of it.

To check that, run sp_Blitz (disclaimer: free open source script I wrote) and one of the warnings is MSDB History Not Purged, which warns if you've got more than 60 days of history stored.

To fix it, add a History Cleanup Task to your maintenance plans. However, if you're in this spot - where updating this table is taking forever - then clearing out history is probably going to take forever, too. You may have to run sp_purge_jobhistory manually yourself, nibbling off a little history at a time, to keep downtime low.