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