Database Maintenance Failure

What version are you running?  You don't mention that in the post.

Sorry.. We are running 202.4.2.  Also, I forgot to said that our Database is in a SQL cluster.. this is the only instance having issues.  

Your version number got truncated.  Are you running 2023.4.2?

yes

I think I remember this being an issue with this particular release.  Is there a reason you haven't updated to 2024.1?  That's what I'm running and I just checked the DB Maintenance logs and those issues you report aren't in the logs anywhere.

Thanks... I'll try that this weekend.  Been working with support on this issue for sometime now and it's frustrating. 

Hello and GM KMSigma. I have a support case open (01592717) on a similar issue. We are running 2024.1 and seeing this in the logs.                                  "Database maintenance started on Thursday, March 28, 2024 has finished with errors."                                                                                                       2024-03-28 02:19:05,301 [1] WARN SolarWinds.Data.DatabaseMaintenance.OrionNotificationHelper - Detected that operation [Summarize detailed data]-[InterfaceAvailability] failed 3 times in a row.
2024-03-28 02:19:05,301 [1] WARN SolarWinds.Data.DatabaseMaintenance.OrionNotificationHelper - Detected that operation [Summarize hourly data]-[InterfaceAvailability] failed 3 times in a row.

I just upgraded to 2024.1 and so far no errors with the database.  Here's what support sent to me to try:

~~~~~~~~~~~~~~

Note: Please save a backup of your Orion Database and a snapshot of the Orion server.

Possible Deduplication, The node involved had multiple sysname added based on the database values

Next Step:

Turn off the SysName deduplication mechanism

How to turn off the SysName de-duplication mechanism:

Part A

In c:\Program Files (x86)\SolarWinds\Orion\SolarWinds.Orion.Discovery.Job.dll.config there are 3 keys:
So just change the DisableSysNameDuplicateDetector value to True and save the file. Restart Orions Services to take effect.

<add key="DisableMacDuplicateDetector" value="false" />
<add key="DisableSysNameDuplicateDetector" value="true" />
<add key="DisableDNSDuplicateDetector" value="false" />

Part B

In the database, the Settings table there are following settings:

Discovery-Import-DNSDeDuplicator-Weight (default CurrentValue=27)
Discovery-Import-MacDuplicateDetector-Weight (default CurrentValue=26)
Discovery-Import-SysNameDeDuplicator-Weight  (default CurrentValue=25)                     

To disable, one needs to change the Discovery-Import-SysNameDeDuplicator-Weight

- CurrentValue= 0 directly from SQL Management studio in edit mode or by executing following statement:

UPDATE Settings SET CurrentValue = 0 WHERE SettingID = 'Discovery-Import-SysNameDeDuplicator-Weight             
Where applicable, can also use the following:

update [dbo].[Settings] set CurrentValue = 0 where settingid = 'Discovery-Import-DNSDeDuplicator-Weight'
update [dbo].[Settings] set CurrentValue = 0 where settingid = 'Discovery-Import-MacDuplicateDetector-Weight'

The Sysname deduplicator should be bypassed by these steps.

Thanks Clark1812! I sent support this link to see if similar to my DB maintenance error. I'll post back later.

Hello Clark1812. Thanks for the information. Just wanted to let you know I am working with SW's support on this. This is their response. I'll update things here after we fix the problem. Hi Rick,

Upon checking the Thwack post I don't recommend to try the same thing as the node values may defer. Let me pull additional resources on this for further analysis and recommendations.

Thanks in advance.

Regards,
Technical Support

 

the latest update resolved 90% of the database issues but still can't remove a node from the database.  Any suggestions? 

Here is what support wants me to do for the issue I reported. If anyone is interested. Always a good idea to run things by SW's support first. Your problem may be different. Seems this is a know bug. I upgraded from 2023.4.2 to 2024.1 and noticed the DB Maintenance report failing and db maint logs showing failures.

Hi Rick,

Thank you for the prompt response.

Yes, this was found to be a known bug.

Hi Rick,

Good day.

Please follow the steps below to fix the issue:

Note: Please backup your database first and and get a snapshot of the server prior to doing the steps


Run the following scripts from the Database Manager

Run the following scripts from the Database Manager

NEW_dbm_InterfaceAvailability_DetailToHourly:
 

 SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO    ALTER PROCEDURE [dbo].[dbm_InterfaceAvailability_DetailToHourly]       @id int = 0,     @date datetime,     @source_table NVARCHAR,     @destination_table NVARCHAR AS BEGIN    SET NOCOUNT ON; SET ROWCOUNT 0;      DECLARE @days INT DECLARE @ChunkSize as INT SET @ChunkSize = 100000 SELECT @days = CurrentValue FROM Settings WHERE SettingID = 'NPM_Settings_InterfaceAvailability_Retain_Detail'      SET @date = DATEADD(day, -@days, dbo.DateOnly(GETDATE()))   BEGIN TRANSACTION     INSERT INTO InterfaceAvailability_Hourly (           [DateTime],           [InterfaceID],           [NodeID],           [Availability],           [Weight])        SELECT           [DateTime],           InterfaceId as [InterfaceId],           NodeId as [NodeId],           SUM (availability * weight) / sum(weight) as [Availability],           SUM ([weight]) as [Weight]         FROM (         SELECT         CAST(FLOOR(CAST([DateTime] AS float)*24)/24 AS smalldatetime) AS [DateTime]         ,InterfaceId         ,NodeId         ,availability         ,[weight]                               FROM InterfaceAvailability_Detail                     WHERE ([DateTime] < @date)     )IA_Detail     GROUP BY NodeId, InterfaceId, [DateTime]        SET ROWCOUNT @ChunkSize;     NextChunk:           DELETE FROM InterfaceAvailability_Detail WHERE ([DateTime] < @date)     IF @@ROWCOUNT = @ChunkSize GOTO NextChunk COMMIT TRANSACTION    SET ROWCOUNT 0;    END          -- Scripts are not supported under any SolarWinds support program or service. -- Scripts are provided AS IS without warranty of any kind. SolarWinds further -- disclaims all warranties including, without limitation, any implied warranties -- of merchantability or of fitness for a particular purpose. The risk arising -- out of the use or performance of the scripts and documentation stays with you. -- In no event shall SolarWinds or anyone else involved in the creation, -- production, or delivery of the scripts be liable for any damages whatsoever -- (including, without limitation, damages for loss of business profits, business -- interruption, loss of business information, or other pecuniary loss) arising -- out of the use of or inability to use the scripts or documentation.

NEW_dbm_InterfaceAvailability_HourlyToDaily
 

    SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO    ALTER PROCEDURE [dbo].[dbm_InterfaceAvailability_HourlyToDaily]       @id int = 0,     @date datetime,     @source_table NVARCHAR,     @destination_table NVARCHAR AS BEGIN    SET NOCOUNT ON; SET ROWCOUNT 0;      DECLARE @days INT DECLARE @ChunkSize as INT SET @ChunkSize = 100000 SELECT @days = CurrentValue FROM Settings WHERE SettingID = 'NPM_Settings_InterfaceAvailability_Retain_Hourly'      SET @date = DATEADD(day, -@days, dbo.DateOnly(GETDATE()))    BEGIN TRANSACTION     INSERT INTO InterfaceAvailability_Daily (         [DateTime],         [InterfaceID],         [NodeID],         [Availability],         [Weight])        SELECT         [DateTime],         InterfaceId as [InterfaceId],         NodeId as [NodeId],         SUM (availability * weight) / sum(weight) as [Availability],         SUM ([weight]) as [Weight]         FROM (         SELECT         CAST(FLOOR(CAST([DateTime] AS float)) AS smalldatetime) AS [DateTime]         ,InterfaceId         ,NodeId         ,availability         ,[weight]         FROM InterfaceAvailability_Hourly         WHERE ([DateTime] < @date)     ) IA_Daily     GROUP BY NodeId, InterfaceId, [DateTime]        SET ROWCOUNT @ChunkSize;     NextChunk:           DELETE FROM InterfaceAvailability_Hourly WHERE ([DateTime] < @date)     IF @@ROWCOUNT = @ChunkSize GOTO NextChunk COMMIT TRANSACTION    SET ROWCOUNT 0;    END          -- Scripts are not supported under any SolarWinds support program or service. -- Scripts are provided AS IS without warranty of any kind. SolarWinds further -- disclaims all warranties including, without limitation, any implied warranties -- of merchantability or of fitness for a particular purpose. The risk arising -- out of the use or performance of the scripts and documentation stays with you. -- In no event shall SolarWinds or anyone else involved in the creation, -- production, or delivery of the scripts be liable for any damages whatsoever -- (including, without limitation, damages for loss of business profits, business -- interruption, loss of business information, or other pecuniary loss) arising -- out of the use of or inability to use the scripts or documentation.
Once completed, wait until the database maintenance completes, and let us know how it goes.

Here is what support wants me to do for the issue I reported. If anyone is interested. Always a good idea to run things by SW's support first. Your problem may be different. Seems this is a know bug. I upgraded from 2023.4.2 to 2024.1 and noticed the DB Maintenance report failing and db maint logs showing failures.

Hi Rick,

Thank you for the prompt response.

Yes, this was found to be a known bug.

Hi Rick,

Good day.

Please follow the steps below to fix the issue:

Note: Please backup your database first and and get a snapshot of the server prior to doing the steps


Run the following scripts from the Database Manager

Run the following scripts from the Database Manager

NEW_dbm_InterfaceAvailability_DetailToHourly:
 

 SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO    ALTER PROCEDURE [dbo].[dbm_InterfaceAvailability_DetailToHourly]       @id int = 0,     @date datetime,     @source_table NVARCHAR,     @destination_table NVARCHAR AS BEGIN    SET NOCOUNT ON; SET ROWCOUNT 0;      DECLARE @days INT DECLARE @ChunkSize as INT SET @ChunkSize = 100000 SELECT @days = CurrentValue FROM Settings WHERE SettingID = 'NPM_Settings_InterfaceAvailability_Retain_Detail'      SET @date = DATEADD(day, -@days, dbo.DateOnly(GETDATE()))   BEGIN TRANSACTION     INSERT INTO InterfaceAvailability_Hourly (           [DateTime],           [InterfaceID],           [NodeID],           [Availability],           [Weight])        SELECT           [DateTime],           InterfaceId as [InterfaceId],           NodeId as [NodeId],           SUM (availability * weight) / sum(weight) as [Availability],           SUM ([weight]) as [Weight]         FROM (         SELECT         CAST(FLOOR(CAST([DateTime] AS float)*24)/24 AS smalldatetime) AS [DateTime]         ,InterfaceId         ,NodeId         ,availability         ,[weight]                               FROM InterfaceAvailability_Detail                     WHERE ([DateTime] < @date)     )IA_Detail     GROUP BY NodeId, InterfaceId, [DateTime]        SET ROWCOUNT @ChunkSize;     NextChunk:           DELETE FROM InterfaceAvailability_Detail WHERE ([DateTime] < @date)     IF @@ROWCOUNT = @ChunkSize GOTO NextChunk COMMIT TRANSACTION    SET ROWCOUNT 0;    END          -- Scripts are not supported under any SolarWinds support program or service. -- Scripts are provided AS IS without warranty of any kind. SolarWinds further -- disclaims all warranties including, without limitation, any implied warranties -- of merchantability or of fitness for a particular purpose. The risk arising -- out of the use or performance of the scripts and documentation stays with you. -- In no event shall SolarWinds or anyone else involved in the creation, -- production, or delivery of the scripts be liable for any damages whatsoever -- (including, without limitation, damages for loss of business profits, business -- interruption, loss of business information, or other pecuniary loss) arising -- out of the use of or inability to use the scripts or documentation.

NEW_dbm_InterfaceAvailability_HourlyToDaily
 

    SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO    ALTER PROCEDURE [dbo].[dbm_InterfaceAvailability_HourlyToDaily]       @id int = 0,     @date datetime,     @source_table NVARCHAR,     @destination_table NVARCHAR AS BEGIN    SET NOCOUNT ON; SET ROWCOUNT 0;      DECLARE @days INT DECLARE @ChunkSize as INT SET @ChunkSize = 100000 SELECT @days = CurrentValue FROM Settings WHERE SettingID = 'NPM_Settings_InterfaceAvailability_Retain_Hourly'      SET @date = DATEADD(day, -@days, dbo.DateOnly(GETDATE()))    BEGIN TRANSACTION     INSERT INTO InterfaceAvailability_Daily (         [DateTime],         [InterfaceID],         [NodeID],         [Availability],         [Weight])        SELECT         [DateTime],         InterfaceId as [InterfaceId],         NodeId as [NodeId],         SUM (availability * weight) / sum(weight) as [Availability],         SUM ([weight]) as [Weight]         FROM (         SELECT         CAST(FLOOR(CAST([DateTime] AS float)) AS smalldatetime) AS [DateTime]         ,InterfaceId         ,NodeId         ,availability         ,[weight]         FROM InterfaceAvailability_Hourly         WHERE ([DateTime] < @date)     ) IA_Daily     GROUP BY NodeId, InterfaceId, [DateTime]        SET ROWCOUNT @ChunkSize;     NextChunk:           DELETE FROM InterfaceAvailability_Hourly WHERE ([DateTime] < @date)     IF @@ROWCOUNT = @ChunkSize GOTO NextChunk COMMIT TRANSACTION    SET ROWCOUNT 0;    END          -- Scripts are not supported under any SolarWinds support program or service. -- Scripts are provided AS IS without warranty of any kind. SolarWinds further -- disclaims all warranties including, without limitation, any implied warranties -- of merchantability or of fitness for a particular purpose. The risk arising -- out of the use or performance of the scripts and documentation stays with you. -- In no event shall SolarWinds or anyone else involved in the creation, -- production, or delivery of the scripts be liable for any damages whatsoever -- (including, without limitation, damages for loss of business profits, business -- interruption, loss of business information, or other pecuniary loss) arising -- out of the use of or inability to use the scripts or documentation.
Once completed, wait until the database maintenance completes, and let us know how it goes.

Thanks for the info but the database sits in a SQL Cluster and can't make changes globally.