3

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

|improve this question
  • Use XE to trace the backup activity and pick up any other activity that may be running. – user507 Mar 2 '17 at 17:36
2

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.
|improve this answer
  • We are seeing this on both methods actually. We have been using the native backup to a share on the appliance, but we have also just started a production test using DDBoost to an MTREE, full backup only, and we have seen this same behavior. Version is 3.something – Tim J Mar 2 '17 at 17:44
  • @TimJ Ok, probably not the same issue, then. I'll edit my answer with some other things to check. – BradC Mar 2 '17 at 17:46
  • 1
    You can eliminate the Data Domain (or confirm it) as the cause of the delay by sending your backups somewhere else temporarily. – Max Vernon Mar 2 '17 at 17:50
  • @MaxVernon Seems like a good test, backing up to a local disk (even a temporarily allocated one) eliminates backup appliance and network factors. – BradC Mar 2 '17 at 18:05
  • 2
    So my testing worked better then expected. I adjusted the memory on one server and it had the same issue. On another server I setup a new drive and backed up the databases locally with the fastest backup time in months. It was pretty clear that writing to the DD appliance is the issue. – Tim J Mar 3 '17 at 17:11
3

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.

|improve this answer
  • I don't think that is the case here. We do have a History Cleanup Task as part of the maintenance package, and we only keep 40 days of history. That task takes less than 1 sec to run. The msdb.dbo.backupset table only contains 27,710 rows. – Tim J Mar 2 '17 at 17:14
  • Great! That sounds like you're in the clear. – Brent Ozar Mar 2 '17 at 18:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.