I want you to know about a possible solution, that worked for us, regarding a failed "object properties collection task" in Veeam ONE Reporter:
The problem:
In the Reporter web application under - Configuration - Servers the default scheduled job "object properties collection task" failed every time it ran.
While it was running, there were some objects "processing" for several hours while the rest was failed.
After 6 hours the timeout canceled the task and all objects were failed.
I opened a case (#03108570) and the support told me to increase the timeout to 10 hours.
The job again failed with following details:
Code: Select all
25.07.2018 17:50:04 [info]: Collection task 'CLUSTERNAME' has finished with Success.
25.07.2018 17:50:04 [info]: Collection task 'CLUSTERNAME' has finished with Success.
25.07.2018 17:50:04 [info]: Collection task 'CLUSTERNAME' has finished with Success.
25.07.2018 17:50:04 [info]: Collection task 'CLUSTERNAME' has finished with Success.
25.07.2018 17:50:04 [info]: Collection task 'CLUSTERNAME' has finished with Success.
25.07.2018 17:50:27 [info]: Updating aggregated properties
25.07.2018 23:29:27 [error]: Server collection session has timed out
26.07.2018 00:46:21 [info]: Failed to save the collected object properties.Thread was being aborted.
26.07.2018 02:51:49 [error]: Collection has failed. Performing rollback to the previous session state...
26.07.2018 02:51:49 [error]: Collection has failed. Performing rollback to the previous session state...
26.07.2018 02:51:49 [info]: Rollback to the previous session state has been completed.
26.07.2018 02:51:49 [info]: Rollback to the previous session state has been completed.They asked me to monitor the (dedicated) SQL server where the Veeam ONE database resides.
But that was not the bottleneck, even when there are other databases running on it.
The SQL server runs on a Microsoft S2D Cluster with 2 NVMEs and 12 SSDs in each Node.
The next day the "object properties collection task" surprinsingly finished successfully (within 8 minutes) for the first time, although there was no change except the increase of the timeout.
The conclusion from the support after that was, that the disks of the SQL server must be the bottleneck.
Possible solution:
I asked my SQL collegue for his expertise:
While the "object properties collection task" was running he saw a lock on the VeeamOne Database in MS SQL Management Studio.
There were two statements running against the VeeamOne database:
Statement SPID 520:
VeeamOne.monitor.sp_get_bv_objects;1
Statement SPID 524 (this one was blocked by SPID 520):
insert bulk reporter.Object ([ObjectKey] NVarChar(1024) COLLATE SQL_Latin1_General_CP1_CI_AS, [TypeID] TinyInt, [CreationID] Int, [DeletionID] Int) with (TABLOCK)
After that he analyzed the Stored Procedure [monitor].[sp_get_bv_objects]:
In it there is a SELECT statement, which can be analyzed with the SQL Management Studio function "Display Estimated Execution Plan" (CTRL+L).
This returned a suggestion to create a missing index:
/*
Missing Index Details from SQLQuery7.sql - SQLSERVER.VeeamOne (DOMAIN\USERNAME (399))
The Query Processor estimates that implementing the following index could improve the query cost by 23.7809%.
*/
/*
USE [VeeamOne]
GO
CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
ON [reporter].[ObjectProperty] ([PropertyID])
INCLUDE ([ObjectID],[Value])
GO
*/
After we created that index, the "object properties collection task" finished within several minutes, like once before.
Until now, we never had the problem that it failed.
BTW it seems, that we solved another issue with that:
When we started maintenance on a Cluster or Server object, the maintenance was set immediately, but when we wanted to exit the mainentance mode, it was never done --> Veeam ONE server reboot was necessary
This now also works fine, after the creation of the index.
We hope that the Veeam developers may find some improvements in our finding, so that this problem can be seen as solved in the future.
We also hope, that the creation of the index is not preventing the future upgrades of Veeam ONE from installing correctly!?!?
How should we proceed with that index?
Regards
Weiss Christian