Start a Conversation

This post is more than 5 years old

Solved!

Go to Solution

2736

February 23rd, 2017 07:00

SQL Agent slow

We are currently running Avamar 7.3.0-233 to back up our environment to our Data Domain system. We have a few SQL Servers running on physical hardware (not in a VM) and when we run transaction log backups they are extremely slow.

One of our current test environment SQL Servers we are trying to back up has a 200GB database with little change during the day, transaction logs are about 2-10MB in size due to this. It's a mirror of our live system, so we want to set it up close as possible to that to do testing.

When I back up the transaction logs manually in SQL Server Management Studio (SSMS) it takes a whole 10 seconds to complete.

When I build the policy out and the data sets for only that database in avamar, it takes 16 minutes!!! to complete. To the point our every 15 minute transaction log backups start piling up in queue and you get a never ending transaction log backup process.

To compare the Full backup that happens nightly if I run it manually it takes 25 minutes to run in SSMS. In avamar it's taking 47 minutes to complete.

Most of this time during the transaction log backups via avamar the CPU usage is at 0% on all CPU's, the disk usage is nothing, disk queus are zero, and network interfaces are almost nothing. So it does not seem like a hardware issue. It's the complete opposite with the SSMS backup, during that time disk and cpu spike until completion.

In avamar I set up the transaction log data set like this:

Source: SQL

Inclusions: (local)/TestDatabase/

Windows SQL Plugin options:

Backup Label: SQLServer_TransacitonLogs

Backup type: incremental

Availability Group: Primary

Enhanced Data deduplication: Disabled (I've tried both with no change in speed)

* Force Incremental backup after full backup - Checked

Maximum number of streams 4

Minimum stream size: one stream per database (i've tried different combinations but nothing changed)

Truncate Database log: (defualt) only for incremental backup

simple model database: skip with warning

encryption to data domain: high (we need AES-256 by legal requirements)

Anything I can look at to see whats up? When we first started testing these backups via avamar took 2 minutes which was ok, now 15+ is ridiculous with the same amount of data backed up in a transaction log backup

To add to this I noticed there is a 4 minute pause between  when it starts "working on target" and when it gets the next line of the last full backup found.. (ex: 9:39:38 then next is 9:43:23) this happens with EVERY database... no wonder it's taking so long, what is going on here?

2017-02-23 09:39:38 avsql Info <6837>: Excluding target '(local)/tempdb'

2017-02-23 09:39:38 avsql Info <7065>: Working on target '(local)/ReportServer_BenefitsTempDB'

2017-02-23 09:39:38 avsql Warning <40418>: Skipping database '(local)/ReportServer_BenefitsTempDB' due to the following reason: Database '(local)/ReportServer_BenefitsTempDB' is in SIMPLE recovery model.

2017-02-23 09:39:38 avsql Info <7065>: Working on target '(local)/ReportServer_Benefits'

2017-02-23 09:43:23 avsql Info <40399>: Last full backup of target '(local)/ReportServer_Benefits' was found, labelnum = '9404'.

2017-02-23 09:43:32 avsql Info <40401>: Only the LSN was verified, during log gap detection.

2017-02-23 09:43:32 avsql Info <14293>: Creating a database task for '(local)/ReportServer_Benefits/i-15' (hcount 360)

2017-02-23 09:43:32 avsql Info <7065>: Working on target '(local)/msdb'

2017-02-23 09:43:32 avsql Warning <40418>: Skipping database '(local)/msdb' due to the following reason: Database '(local)/msdb' is in SIMPLE recovery model.

2017-02-23 09:43:32 avsql Info <7065>: Working on target '(local)/model'

2017-02-23 09:47:18 avsql Info <40399>: Last full backup of target '(local)/model' was found, labelnum = '9404'.

2017-02-23 09:47:18 avsql Info <40401>: Only the LSN was verified, during log gap detection.

2017-02-23 09:47:18 avsql Info <14293>: Creating a database task for '(local)/model/i-15' (hcount 407)

18 Posts

March 20th, 2017 04:00

Hi,

From the described symptoms, I suspect that the time is wasted in listing the entire backup history for the SQL server then start performing the incremental backup.

You can confirm by retiring the SQL server from avamar and re-register it one more time which will keep the backups with the same retention policy assigned to them; put will force a reset for the backup listing; if the backup performance got improved again then its backup history listing issue.

Check the following KB for detailed description about this issue and how to fix it:

496622 : Avamar avsql hourly incremental backup and restore performance severely impacted due to very large backup label numbers https://support.emc.com/kb/496622

Appreciate your update and feedback if this was helpful to solve the problem; don't forget to mark the question as answered when the problem is solved.

Best Regards,

Shady Salama

Subject Matter Expert (SME), Avamar, Data Protection & Availability Solutions

Dell EMC | Technical Support Services

No Events found!

Top