Sql-server – Database Full Backup Maintenance Task has large delay between databases

backupsql serversql server 2014

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

Best Answer

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.