SQL Server hosting VeeamDB high CPU usage (75-100%)

[lenart.plausteiner]

Hi guys.

We are having strange behavior on our external SQL server hosting VeeamBackup DB. After a couple of days (1-2 days) our SQL server CPU usage goes UP to 100% percent of usage and this leads to problems like this below:
[27.12.2016 16:48:14] <01> Error Microsoft SQL server hosting the configuration database is currently unavailable. Possible reasons are heavy load, networking issue, server reboot, or hot backup.
[27.12.2016 16:48:14] <01> Error Please wait, and try again later.

Workaround for this problem is to restart SQL service every 1-2 days. Our SQL server was fine prior to Upgrade to v9.5 and implementing BackupCopy JOB to our new EMC DataDomain box.

SQL Server hosting vCenter DB and Veeam DB is 4vCPU and 8Gb of RAM. Both databases are small, Veeam DB is small as 2,5Gb.

With Veeam support, we already defragmented DB.

We use external DB because we would love to have all System DB of environment on one SQL.

Our Veeam server has its primary Backup Repository on SAN disk, which is Windows De-Dup, enabled. All our JOB’s are configured with Weekly Active full and everyday incremental.

I would love to hear from anyone out there, that had similar experience and has fixed the problem.

We have opened the case at Veeam, Case # is: 02026715

Regards,
Lenart

[Mike Resseler]

Lenart,

I saw in the support case that you are working with the support engineer so please keep doing that. The only thing I can assume that is happening is that a specific task inside the database is demanding all those resources. But with the above information it will be difficult to find what job is running

Certainly some things you can look at:
* Does it always happen around the same time (in other words, a triggered task)
* Is it Ad-hoc (in other words, triggered by something that is not scheduled or different tasks all causing the same issue)
* Is there something in the logs that you can see which database is causing this peak

[lenart.plausteiner]

Hi,
As far as I know for now is:
* This is happening in VeeamBackup DB (I send one screen shoot of %proc time against VeeamBackup, I send it to your technician)
* It happens every 1-1,5 days (I'm almost sure that is not always the same time)

SQL Admin checked the logs and there should be no problems with SQL itself
He exposed one strange query against SQL server, and we are not doing any Oracle backups for now

Code: Select all

SELECT  c.oib_id,
                                               c.id,
                            c.usn,
                                               s.[path],
                                               s.size,
                                               s.scn_from,
                                               s.scn_to,
                                               s.utc_time_from,
                                               s.utc_time_to,
                                               s.seq_num,
                                               s.database_id
                    INTO    #tempOracleArcLogs
                    FROM    [dbo].[Backup.Model.OibOracleArchiveLogs] c
                                        JOIN [dbo].[Backup.Model.OibOracleArchiveLogsSensitiveInfo] s on s.oib_oracle_archive_logs_id = c.id
                    WHERE   c.oib_id IN (SELECT id FROM #tempOibIds) AND
                            c.usn >= @usn AND
                            c.id NOT IN (SELECT id FROM #tempOracleArcLogsEncrypted)
                                  --------------------------------------------
                                  --Crypto

Today I have moved the DB back to the backup server to SQL Express, I hope that this will help. I'm not admin on that SQL box so I can't change some settings.

[Mike Resseler]

Lenart,

Thanks for the additional information. Please keep us informed about the outcome. (And yes, that SQL query seems strange)

[alexander sh]

Hey Lenart,
there's nothing mysterious about this particular SQL query, it's just a part of our metadata generation procedure. It grabs all the necessary data from VBR database to write it out to disk to .vbm metadata file. If you don't have any Oracle log jobs, it will simply collect nothing from these tables.
Judging from the log files, it seems the problem indeed has to do with this metadata generation SQL procedure. We've seen something similar a couple times after upgrade to 9.5.
Do I understand correctly that the issue still persists?
There's a trick that probably is going to help with SQL recourse utilization. It's included in 9.5 U1 which should be available soon, but I'll go ahead and ask one of Tier 2 support engineers to grab your case and implement that fix to see if it actually helps.

[lenart.plausteiner]

Hey,

Yes, the problem is still here. I've moved VeeamDB to local SQL instance, as proposed from your support, and after two days of running problem is back. Now I'll leave system as is (no reboots or restarts of SQL service) I hope that backup will survive and all jobs will finish today, but I doubt it .

Just to clarify, problem is, that JOB that should run in one moment can't reach SQL Server, server is there and available (I can connect with SQL Management studio and browse tables).

[alexander sh]

Hey Lenart,

I see from the support case notes that you've installed 9.5 U1, so let's see if it helps. If the problem persists, we'll make sure to arrange a remote session with you to get to the root of the issue.
Thanks!

[lenart.plausteiner]

It seems that basic problem is solved with Update 1. Now we don't have any "phantom" CPU utilization without any running jobs as we had prior installation of update.

Trough the weekend we had some problems with system resources, especially with RAM, this is because we moved SQL DB from external SQL server to local instance last week, as proposed by support and we didn't added additional RAM for this workload. Now I've added additional RAM to this VM, after this action everything worked till now.

I have send one additional log to your support, I think that they will confirm my assumption.

[alexander sh]

That's great to hear, appreciate the update!
If the problems comes back, please don't hesitate to bump this thread.

[steinscherer]

Hi all
Having the same behavior and fighting about 1 month with this issue:
-Restarting SQL Service
-Cleanup Database
-Stopped unnecessary Veeam Services
-Update SQL Server from Express to Standard
….. does not help (

Analyzed the same SQL query (SELECT c.oib_id ) which causing the high SQL server cpu load
Hopefully 9.5 Update 1 will help

[foggy]

Update 1 is available for download, so you can install it and check whether the issue persists.